From SQL Tables to Typed Models: Automate Your Boilerplate

Every application that talks to a database has a mapping layer — SQL columns on one side, typed models on the other. When you create a users table with 15 columns, you also need a User type in your application code. And when you add a column next month, you need to update both. This duplication is tedious, error-prone, and completely automatable.

The problem with manual mapping

Consider a straightforward table:

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price INTEGER NOT NULL,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    is_available BOOLEAN DEFAULT 1,
    weight_kg REAL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

The corresponding TypeScript interface, written by hand:

interface Product {
  id: number;
  name: string;
  description: string;  // Bug: should be string | null
  price: number;
  categoryId: number;
  isAvailable: boolean;
  weightKg: number;     // Bug: should be number | null
  createdAt: string;
  updatedAt: string;
}

Two bugs already. description and weight_kg are nullable in the database (no NOT NULL constraint), but the interface marks them as required. These bugs will not surface until a row with null values hits the application at runtime.

Manual mapping also introduces naming inconsistencies. Did the team agree on categoryId or category_id? isAvailable or is_available? Without automation, every developer makes their own choice.

Type mapping rules

The mapping from SQL types to application types follows predictable rules:

SQL to TypeScript

SQL to Python

Nullable handling

This is where most manual mappings go wrong. The rule is simple:

• Column has NOT NULL constraint: the type is T
• Column lacks NOT NULL (or has DEFAULT NULL): the type is T | null (TypeScript) or Optional[T] (Python)

// Correct: description is nullable
interface Product {
  id: number;
  name: string;              // NOT NULL -> required
  description: string | null; // nullable -> string | null
  price: number;              // NOT NULL -> required
  weightKg: number | null;    // nullable -> number | null
}

# Python with dataclasses
from dataclasses import dataclass
from typing import Optional

@dataclass
class Product:
    id: int
    name: str
    description: Optional[str]
    price: int
    weight_kg: Optional[float]

Automated generators read the DDL constraints and get this right every time.

Naming conventions

SQL traditionally uses snake_case. TypeScript uses camelCase. Python uses snake_case (matching SQL). A good generator handles the conversion:

ORM decorators

If your project uses an ORM, you need more than plain types — you need decorated classes. Here is a Drizzle ORM example:

export const products = sqliteTable('products', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  name: text('name').notNull(),
  description: text('description'),
  price: integer('price').notNull(),
  categoryId: integer('category_id').notNull().references(() => categories.id),
});

Each ORM has its own syntax for primary keys, foreign keys, defaults, and constraints. A generator that understands these patterns saves significant boilerplate.

Relations

Foreign keys in SQL map to relations in application code. The REFERENCES clause tells you:

• category_id INTEGER REFERENCES categories(id) means Product has a many-to-one relation with Category
• A join table like product_tags(product_id, tag_id) means Product has a many-to-many relation with Tag

Automated generators can detect these patterns and produce the appropriate relation decorators or type definitions.

The workflow

The most efficient approach is:

1. Write your SQL schema (the source of truth)
2. Generate typed models automatically
3. Regenerate whenever the schema changes
4. Never edit generated files by hand

This keeps your database and application types permanently in sync with zero manual effort.

Try our SQL to Models Generator to convert your DDL to typed models instantly — right in your browser, no upload required.