Generating Python Dataclasses from SQL Database Schemas

Python dataclasses (introduced in Python 3.7) are the modern, idiomatic way to represent database rows as Python objects. They provide automatic __init__, __repr__, and __eq__ methods, work seamlessly with type checkers like mypy, and are compatible with libraries like Pydantic, SQLAlchemy, and dataclasses-json.

This guide shows you exactly how to convert SQL CREATE TABLE statements into Python dataclasses with proper type hints and field defaults.

Why Use Dataclasses for Database Models?

Before Python 3.7, developers typically used regular classes with __init__ methods or NamedTuples. Dataclasses offer several advantages for database models:

• Auto-generated boilerplate: No manual __init__, __repr__, or __eq__
• Type hints built-in: Each field has a declared type, enabling mypy and IDE support
• Field defaults: Default values and factory functions via field()
• Mutable by default: Unlike NamedTuples, you can update field values after creation
• Frozen option: @dataclass(frozen=True) for immutable value objects

The Core SQL to Python Type Mapping

SQL Type	Python Type	Import
INTEGER / INT	int	built-in
BIGINT	int	built-in (arbitrary precision)
FLOAT / REAL	float	built-in
DECIMAL / NUMERIC	Decimal	from decimal import Decimal
VARCHAR / TEXT	str	built-in
BOOLEAN	bool	built-in
TIMESTAMP / DATETIME	datetime	from datetime import datetime
DATE	date	from datetime import date
TIME	time	from datetime import time
UUID	UUID	from uuid import UUID
JSONB / JSON	dict[str, Any]	from typing import Any
BYTEA / BLOB	bytes	built-in

Handling Nullable Columns with Optional

SQL columns without NOT NULL constraints can be NULL. In Python, this maps to Optional[Type] (or Type | None in Python 3.10+) with a default of None:

from dataclasses import dataclass, field
from typing import Optional

@dataclass
class Customer:
    # NOT NULL columns — required at construction
    id: int
    first_name: str
    last_name: str
    email: str

    # Nullable columns — optional, default to None
    phone: Optional[str] = None
    company: Optional[str] = None

In Python 3.10+, you can use the cleaner union syntax:

@dataclass
class Customer:
    phone: str | None = None

Complete Example: PostgreSQL Schema to Dataclass

Consider this PostgreSQL schema:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  stock_count INTEGER NOT NULL DEFAULT 0,
  is_available BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sku UUID DEFAULT gen_random_uuid(),
  metadata JSONB
);

The equivalent Python dataclass:

from __future__ import annotations
from dataclasses import dataclass, field
from datetime import datetime
from decimal import Decimal
from typing import Any, Optional
from uuid import UUID


@dataclass
class Product:
    # Required fields (NOT NULL without DEFAULT)
    id: int
    name: str
    price: Decimal

    # NOT NULL with DEFAULT — optional at construction
    stock_count: int = 0
    is_available: bool = True

    # Auto-generated — use None as sentinel for DB-assigned values
    created_at: Optional[datetime] = None
    sku: Optional[UUID] = None

    # Truly nullable
    description: Optional[str] = None
    metadata: Optional[dict[str, Any]] = None

Handling Auto-Generated Primary Keys

For auto-increment primary keys (SERIAL, INT AUTO_INCREMENT), the value is generated by the database, not the application. The cleanest pattern is to make the id field optional with a default of None and only set it after the row is inserted:

@dataclass
class Product:
    name: str
    price: Decimal
    # id is None before INSERT, set after DB assignment
    id: Optional[int] = field(default=None)

Using Dataclasses with Database Libraries

With psycopg3 (PostgreSQL)

import psycopg
from psycopg.rows import class_row

with psycopg.connect("postgresql://...") as conn:
    with conn.cursor(row_factory=class_row(Product)) as cur:
        cur.execute("SELECT * FROM products WHERE id = %s", (1,))
        product: Product = cur.fetchone()
        print(product.name)  # Fully typed!

With SQLAlchemy 2.0

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
    pass

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    price: Mapped[Decimal] = mapped_column(Numeric(10, 2))

Decimal vs Float for Money

Never use float for monetary values or any SQL DECIMAL/NUMERIC column. Python's float uses binary floating-point which cannot represent most decimal fractions exactly:

# Wrong — floating-point precision error
total: float = 19.99 + 0.01  # → 20.000000000000004

# Correct — exact decimal arithmetic
from decimal import Decimal
total: Decimal = Decimal('19.99') + Decimal('0.01')  # → 20.00

Generate Dataclasses Automatically

Instead of writing dataclasses by hand, use SchemaForge's free SQL to Python dataclass generator. Paste your CREATE TABLE statements and get correctly typed dataclasses with Optional fields, proper imports, and field() defaults — instantly in your browser.