supabase-typegen

supabase-typegen

🚀 Advanced SQL-to-TypeScript type generator for Supabase projects with comprehensive schema support

Generate type-safe TypeScript definitions from your Supabase SQL migration files with support for tables, enums, functions, composite types, JSONB inference, indexes, and more.

✨ Features

Core Type Generation

• ✅ Tables - Full support for Row, Insert, and Update types
• ✅ Enums - Type-safe enum definitions with runtime constants
• ✅ Functions - Function signatures with typed arguments and returns
• ✅ Composite Types - PostgreSQL composite type definitions
• ✅ Views - Read-only view types with intelligent type inference (including materialized views)
• ✅ Indexes - Index metadata (optional with --include-indexes)

Advanced Features

• ✅ JSONB Type Inference - Automatically infer TypeScript types from JSONB defaults
• ✅ Nested Type Extraction - Deep type extraction for complex JSONB structures
• ✅ Geometric Types - Point, Line, Box, Circle, Polygon support
• ✅ Relationships - Foreign key relationships with one-to-one detection
• ✅ Comments - Preserve SQL comments as JSDoc
• ✅ Multi-Schema - Support for multiple database schemas
• ✅ Type Deduplication - Automatic removal of duplicate JSONB types

Developer Experience

• 🎨 Naming Conventions - preserve, PascalCase, camelCase, snake_case, SCREAMING_SNAKE_CASE
• 📝 Prettier Integration - Respects your Prettier configuration
• 🔤 Alphabetical Sorting - Deterministic output ordering
• 🎯 Smart Defaults - Sensible defaults, minimal configuration needed
• 📦 Runtime Constants - Enum values for dropdowns and validation

📦 Installation

npm install supabase-typegen --save-dev
# or
yarn add -D supabase-typegen
# or
pnpm add -D supabase-typegen

🚀 Quick Start

Basic Usage

# Generate types from default Supabase directory
npx supabase-typegen

# Specify custom workdir
npx supabase-typegen --local ./my-supabase

# Custom output directory
npx supabase-typegen --output ./src/types

Using Generated Types

import type {
    Database,
    Tables,
    TablesInsert,
    TablesUpdate,
    Enums,
} from "./database";

// Table types
type User = Tables<"users">;
type UserInsert = TablesInsert<"users">;
type UserUpdate = TablesUpdate<"users">;

// Enum types
type UserStatus = Enums<"user_status">;

// Access nested types
type UserRow = Database["public"]["Tables"]["users"]["Row"];

// With Supabase client
import { createClient } from "@supabase/supabase-js";

const supabase = createClient<Database>(url, anonKey);

// Fully typed queries
const { data } = await supabase
    .from("users")
    .select("*")
    .eq("status", "active"); // Type-safe!

📖 Usage

Command Line Options

Source & Schema

# Use local SQL files (default)
npx supabase-typegen --local [workdir]

# Use live database connection
npx supabase-typegen --db --connection-string "postgresql://..."

# Specify target schema
npx supabase-typegen --schema public

Output & Formatting

# Custom output directory
npx supabase-typegen --output ./src/lib/types

# Naming convention
npx supabase-typegen --naming camelCase
# Options: preserve, PascalCase, camelCase, snake_case, SCREAMING_SNAKE_CASE

# Alphabetical sorting
npx supabase-typegen --alphabetical

# Custom indentation
npx supabase-typegen --indent-size 4

# Use Prettier config
npx supabase-typegen --use-prettier

Type Features

# Include index metadata in types
npx supabase-typegen --include-indexes

# Extract nested JSONB types
npx supabase-typegen --extract-nested

# Enable/disable type deduplication
npx supabase-typegen --deduplicate        # Enable (default)
npx supabase-typegen --no-deduplicate     # Disable

# Exclude comments from output
npx supabase-typegen --no-comments

Logging

# Disable verbose logging
npx supabase-typegen --silent
npx supabase-typegen --no-logs
npx supabase-typegen --quiet

Configuration File

The generator reads your supabase/config.toml automatically:

[db.migrations]
schema_paths = [
  "migrations/*.sql",
  "migrations/schemas/**/*.sql"
]

🎯 Type Generation Examples

Tables

Input SQL:

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  status user_status DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now()
);

COMMENT ON TABLE users IS 'Application users';
COMMENT ON COLUMN users.email IS 'User email address';

Generated Types:

/**
 * Application users
 */
users: {
  Row: {
    id: string
    /** User email address */
    email: string
    status: Database['public']['Enums']['user_status']
    created_at: string
  }
  Insert: {
    id?: string
    email: string
    status?: Database['public']['Enums']['user_status']
    created_at?: string
  }
  Update: {
    id?: string
    email?: string
    status?: Database['public']['Enums']['user_status']
    created_at?: string
  }
  Relationships: []
}

Enums

Input SQL:

CREATE TYPE user_status AS ENUM ('active', 'inactive', 'pending');

Generated Types:

Enums: {
    user_status: "active" | "inactive" | "pending";
}

// Runtime constants
Constants: {
    public: {
        Enums: {
            user_status: ["active", "inactive", "pending"];
        }
    }
}

Functions

Input SQL:

CREATE FUNCTION get_user_posts(user_id UUID, limit_count INT DEFAULT 10)
RETURNS TABLE (id UUID, title TEXT, created_at TIMESTAMPTZ)
LANGUAGE SQL;

Generated Types:

Functions: {
  get_user_posts: {
    Args: {
      user_id: string
      limit_count?: number
    }
    Returns: {
      id: string
      title: string
      created_at: string
    }[]
  }
}

JSONB Types

Input SQL:

CREATE TABLE products (
  id UUID PRIMARY KEY,
  metadata JSONB DEFAULT '{"tags": [], "featured": false, "rating": 0}'::jsonb
);

Generated Types:

// Automatically inferred JSONB type
export type products_metadata = {
    tags: unknown[];
    featured: boolean;
    rating: number;
};

// Merged into table
products: {
    Row: {
        id: string;
        metadata: products_metadata | null;
    }
    // ...
}

Views

Input SQL:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE VIEW active_users AS
SELECT
  id,
  email,
  created_at,
  COUNT(*) OVER () as total_count
FROM users
WHERE deleted_at IS NULL;

Generated Types:

Views: {
    active_users: {
        Row: {
            id: string;
            email: string;
            created_at: string;
            total_count: number;
        }
        Relationships: [];
    }
}

Relationships

Input SQL:

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE(user_id, id)  -- One-to-one relationship detected
);

Generated Types:

posts: {
  Row: { /* ... */ }
  Insert: { /* ... */ }
  Update: { /* ... */ }
  Relationships: [
    {
      foreignKeyName: "posts_user_id_fkey"
      columns: ["user_id"]
      isOneToOne: true
      referencedRelation: "users"
      referencedColumns: ["id"]
    }
  ]
}

Composite Types

Input SQL:

CREATE TYPE address AS (
  street TEXT,
  city TEXT,
  zip TEXT
);

Generated Types:

CompositeTypes: {
    address: {
        street: string;
        city: string;
        zip: string;
    }
}

🎨 Naming Conventions

The generator supports multiple naming conventions for generated types:

# Original SQL names (default)
npx supabase-typegen --naming preserve
# → user_profiles, created_at

# PascalCase
npx supabase-typegen --naming PascalCase
# → UserProfiles, CreatedAt

# camelCase
npx supabase-typegen --naming camelCase
# → userProfiles, createdAt

# snake_case
npx supabase-typegen --naming snake_case
# → user_profiles, created_at

# SCREAMING_SNAKE_CASE
npx supabase-typegen --naming SCREAMING_SNAKE_CASE
# → USER_PROFILES, CREATED_AT

🔍 Advanced Features

JSONB Type Inference

The generator can automatically infer TypeScript types from JSONB default values:

-- Simple JSONB
settings JSONB DEFAULT '{"theme": "dark", "notifications": true}'::jsonb

-- Generated:
export type table_settings = {
  theme: string
  notifications: boolean
}

-- Nested JSONB (with --extract-nested)
metadata JSONB DEFAULT jsonb_build_object(
  'user', jsonb_build_object('name', '', 'age', 0),
  'preferences', jsonb_build_object('theme', 'light')
)

-- Generated (nested types extracted):
export type table_metadata_user = {
  name: string
  age: number
}

export type table_metadata_preferences = {
  theme: string
}

export type table_metadata = {
  user: table_metadata_user
  preferences: table_metadata_preferences
}

Index Metadata

With --include-indexes, index information is included in the generated types:

users: {
  Row: { /* ... */ }
  Insert: { /* ... */ }
  Update: { /* ... */ }
  Relationships: []
  Indexes: [
    {
      name: "users_email_idx"
      columns: ["email"]
      isUnique: true
      method: "btree"
    },
    {
      name: "users_created_at_idx"
      columns: ["created_at"]
      isUnique: false
      method: "btree"
      where: "deleted_at IS NULL"
    }
  ]
}

View Type Inference

Views are automatically parsed with intelligent type inference for columns. The generator analyzes SELECT expressions to determine accurate types:

Input SQL:

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  total NUMERIC NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE VIEW order_stats AS
SELECT
  COUNT(*) as order_count,
  SUM(total) as total_revenue,
  AVG(total) as average_order,
  MAX(created_at) as last_order_date,
  ARRAY_AGG(id) as order_ids,
  total::TEXT as total_text
FROM orders;

-- Materialized views also supported
CREATE MATERIALIZED VIEW daily_sales AS
SELECT
  DATE(created_at) as sale_date,
  COUNT(*) as num_orders,
  SUM(total) as daily_revenue
FROM orders
GROUP BY DATE(created_at);

Note on Materialized Views:

• Materialized views are read-only and do not support INSERT, UPDATE, or DELETE operations
• They must be refreshed using REFRESH MATERIALIZED VIEW
• The generator only produces Row and Relationships types for materialized views

Generated Types:

Views: {
  order_stats: {
    Row: {
      order_count: number             // Inferred from COUNT() (bigint)
      total_revenue: number | null    // Inferred from SUM()
      average_order: number | null    // Inferred from AVG()
      last_order_date: string | null  // Inferred from MAX() - unknown, needs context
      order_ids: string[] | null      // Inferred from ARRAY_AGG()
      total_text: string | null       // Inferred from CAST
    }
    Relationships: []
  }
  daily_sales: {
    Row: {
      sale_date: string | null        // Inferred from DATE()
      num_orders: number              // Inferred from COUNT() (bigint)
      daily_revenue: number | null    // Inferred from SUM()
    }
    Relationships: []
  }
}

Type Inference Rules:

The generator intelligently infers types from common SQL patterns:

• Aggregate Functions: COUNT() → bigint (maps to TypeScript number), SUM()/AVG() → numeric, MIN()/MAX() → unknown (type depends on input)
• Array Functions: ARRAY_AGG() → array type, STRING_AGG() → text
• JSON Functions: JSON_AGG()/JSONB_AGG() → json/jsonb
• Date/Time Functions: NOW() → timestamp with time zone, CURRENT_DATE → date
• Type Casts: column::type and CAST(column AS type) → specified type
• Column References: Direct column references inherit the source table's type
• Complex Expressions: When type cannot be inferred, defaults to unknown

Geometric Types

PostgreSQL geometric types are automatically detected and typed:

CREATE TABLE locations (
  id UUID PRIMARY KEY,
  position POINT,
  area POLYGON
);

// Auto-generated geometric type definitions
export type Point = { x: number; y: number } | string;
export type Polygon = { points: Point[] } | string;

// Used in table types
locations: {
    Row: {
        id: string;
        position: Point | null;
        area: Polygon | null;
    }
}

Multi-Schema Support

Generate types for multiple schemas:

export interface Database {
    public: {
        Tables: {
            /* ... */
        };
        Enums: {
            /* ... */
        };
    };
    auth: {
        Tables: {
            /* ... */
        };
    };
    storage: {
        Tables: {
            /* ... */
        };
    };
}

// Access specific schema
type AuthUser = Database["auth"]["Tables"]["users"]["Row"];

🏗️ Project Structure

your-project/
├── supabase/
│   ├── config.toml
│   └── migrations/
│       ├── 20240101000000_initial.sql
│       ├── 20240102000000_add_users.sql
│       └── 20240103000000_add_posts.sql
├── src/
│   └── lib/
│       └── types/
│           └── generated/
│               └── database.ts  ← Generated types
└── package.json

🤝 Integration Examples

With Supabase Client

import { createClient } from "@supabase/supabase-js";
import type { Database } from "./database";

const supabase = createClient<Database>(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_ANON_KEY!
);

// Fully typed CRUD operations
const { data: users } = await supabase
    .from("users")
    .select("id, email, status")
    .eq("status", "active");

// Type: { id: string; email: string; status: "active" | "inactive" | "pending" }[]

With React Hook Form + Zod

import { useForm } from "react-hook-form";
import type { TablesInsert } from "./database";

type UserInsert = TablesInsert<"users">;

function CreateUserForm() {
    const { register, handleSubmit } = useForm<UserInsert>();

    const onSubmit = async (data: UserInsert) => {
        await supabase.from("users").insert(data);
    };

    return (
        <form onSubmit={handleSubmit(onSubmit)}>
            <input {...register("email")} type="email" required />
            <button type="submit">Create User</button>
        </form>
    );
}

With tRPC

import type { Database, TablesInsert } from "./database";

export const userRouter = router({
    create: publicProcedure
        .input(
            z.object({
                email: z.string().email(),
                name: z.string(),
            })
        )
        .mutation(
            async ({
                input,
            }): Promise<Database["public"]["Tables"]["users"]["Row"]> => {
                const { data } = await supabase
                    .from("users")
                    .insert(input)
                    .select()
                    .single();

                return data;
            }
        ),
});

📚 Helper Types

The generator provides several helper types for convenience:

// Get table Row type
type User = Tables<"users">;
type Post = Tables<"posts">;

// Get Insert type (optional fields for defaults)
type UserInsert = TablesInsert<"users">;
type PostInsert = TablesInsert<"posts">;

// Get Update type (all fields optional)
type UserUpdate = TablesUpdate<"users">;
type PostUpdate = TablesUpdate<"posts">;

// Get Enum type
type UserStatus = Enums<"user_status">;

// Multi-schema support
type AuthUser = Tables<"users", { schema: "auth" }>;

// Composite types
type Address = CompositeTypes<"address">;

🎯 Best Practices

1. Version Control Generated Files

# Don't ignore generated types!
# src/lib/types/generated/database.ts

Commit generated types so team members get type safety without running the generator.

2. Regenerate After Migrations

Add to your migration workflow:

{
    "scripts": {
        "db:migrate": "supabase migration up && npm run types:generate",
        "types:generate": "supabase-typegen"
    }
}

3. Use Type-Safe Queries

// ✅ Good - Fully typed
const { data } = await supabase
    .from("users")
    .select("id, email, posts(title)")
    .eq("status", "active");

// ❌ Bad - Loses type safety
const { data } = await supabase.from("users").select("*");

4. Leverage Enums

import { Constants } from "./database";

// Use runtime enum values for dropdowns
const statusOptions = Constants.public.Enums.user_status.map((status) => ({
    value: status,
    label: status.charAt(0).toUpperCase() + status.slice(1),
}));

🔧 Configuration Options

CLI Flags Summary

Flag	Description	Default
--local [workdir]	Use local SQL files	./supabase
--workdir <path>	Explicit workdir path	-
--db	Use database connection	false
--connection-string <url>	Database connection URL	$DATABASE_URL
--schema <name>	Target schema	public
--output <dir>	Output directory	./src/lib/types/generated
--naming <convention>	Naming convention	preserve
--alphabetical	Sort alphabetically	false
--extract-nested	Extract nested JSONB types	false
--deduplicate	Deduplicate types	true
--no-deduplicate	Disable deduplication	-
--indent-size <n>	Indentation (1-8)	2
--use-prettier	Use Prettier config	false
--include-indexes	Include index metadata	false
--no-comments	Exclude SQL comments	false
--silent	Disable logging	false

🚧 Roadmap

Coming Soon

• Row Level Security (RLS) Policies - Policy metadata in table types
• Check Constraints - Constraint metadata for validation
• Runtime Validators - Zod/Valibot schema generation
• Type Guards - Runtime type checking functions
• Triggers - Trigger metadata
• Domain Types - Custom domain type support
• Range Types - PostgreSQL range type support

Under Consideration

• Database introspection mode (read from live DB)
• Migration diff generator
• GraphQL schema generation
• OpenAPI schema generation

🐛 Known Limitations

• Complex View Expressions: Some advanced SQL expressions in views may infer as unknown type and need manual refinement
• Complex JSONB: Very deep nesting (5+ levels) may need manual type refinement
• Recursive Types: Self-referential types need manual handling
• Computed Columns: Generated/computed columns not yet supported

💡 Tips & Tricks

Custom Type Overrides

Override generated types for special cases:

import type { Database as GeneratedDatabase } from "./generated/database";

// Extend or override types
export interface Database extends GeneratedDatabase {
    public: GeneratedDatabase["public"] & {
        Tables: GeneratedDatabase["public"]["Tables"] & {
            users: {
                Row: GeneratedDatabase["public"]["Tables"]["users"]["Row"] & {
                    // Add computed fields
                    fullName: string;
                };
            };
        };
    };
}

Monorepo Setup

{
    "scripts": {
        "types:generate:app1": "supabase-typegen --workdir ./apps/app1/supabase --output ./apps/app1/src/types",
        "types:generate:app2": "supabase-typegen --workdir ./apps/app2/supabase --output ./apps/app2/src/types",
        "types:generate:all": "npm run types:generate:app1 && npm run types:generate:app2"
    }
}

CI/CD Integration

# .github/workflows/types.yml
name: Check Types

on: [pull_request]

jobs:
    check-types:
        runs-on: ubuntu-latest
        steps:
            - uses: actions/checkout@v3
            - uses: actions/setup-node@v3
            - run: npm ci
            - run: npm run types:generate
            - run: git diff --exit-code src/lib/types/generated/
              name: Verify types are up to date

🤝 Contributing

Contributions are welcome! Please see CONTRIBUTING.md for details.

📄 License

MIT © chizaruu

🙏 Acknowledgments

• Built for Supabase projects
• Inspired by the official supabase gen types command
• Uses battle-tested SQL parsing techniques

📞 Support

• 📖 Documentation
• 💬 Discussions
• 🐛 Issue Tracker

Made with ❤️ for the Supabase community