Type Generation

Generate TypeScript types from your database schema.

Overview

The postbase types generate command introspects your database and creates TypeScript interfaces that perfectly match your schema.

Basic Usage

postbase types generate \
  --database myapp \
  --output ./src/db/types.ts

Options

Option	Description
`--database`	Database name (required)
`--output`	Output file path (required)
`--all`	Include functions, views, and enums
`--include-functions`	Include PostgreSQL functions
`--include-views`	Include database views
`--include-enums`	Include enum types
`--schema`	Target schema (default: public)
`--dates-as-strings`	Map timestamps to string

Generated Types

Table Types

For each table, three interfaces are generated:

export interface Database {
  public: {
    Tables: {
      users: {
        // Full row type (SELECT)
        Row: {
          id: number
          email: string
          name: string
          created_at: Date
          updated_at: Date
        }
        // Insert type (INSERT)
        Insert: {
          email: string
          name: string
          created_at?: Date  // Optional if has default
          updated_at?: Date
        }
        // Update type (UPDATE)
        Update: {
          email?: string
          name?: string
          updated_at?: Date
        }
      }
    }
  }
}

Column Types

PostgreSQL types map to TypeScript:

PostgreSQL	TypeScript
`integer`, `serial`, `smallint`	`number`
`bigint`	`string` (precision)
`real`, `double precision`	`number`
`numeric`, `decimal`	`string`
`boolean`	`boolean`
`varchar`, `text`, `char`	`string`
`timestamp`, `date`, `time`	`Date`
`timestamptz`	`Date`
`json`, `jsonb`	`unknown`
`uuid`	`string`
`bytea`	`Buffer`
`array`	`T[]`

Nullable Columns

Nullable columns use union with null:

{
  description: string | null
  deleted_at: Date | null
}

Default Values

Columns with defaults are optional in Insert:

{
  Insert: {
    email: string         // Required
    created_at?: Date     // Optional (has DEFAULT NOW())
    status?: string       // Optional (has DEFAULT 'pending')
  }
}

Including Enums

postbase types generate \
  --database myapp \
  --include-enums \
  --output ./src/db/types.ts

PostgreSQL:

CREATE TYPE user_role AS ENUM ('user', 'admin', 'moderator');

Generated:

export type UserRole = 'user' | 'admin' | 'moderator'
 
export interface Database {
  public: {
    Enums: {
      user_role: UserRole
    }
    Tables: {
      users: {
        Row: {
          role: UserRole
        }
      }
    }
  }
}

Including Views

postbase types generate \
  --database myapp \
  --include-views \
  --output ./src/db/types.ts

Generated:

export interface Database {
  public: {
    Views: {
      active_users: {
        Row: {
          id: number
          email: string
          name: string
        }
      }
    }
  }
}

Including Functions

postbase types generate \
  --database myapp \
  --include-functions \
  --output ./src/db/types.ts

PostgreSQL:

CREATE FUNCTION get_user_posts(user_id integer, limit_count integer DEFAULT 10)
RETURNS SETOF posts AS $
  SELECT * FROM posts WHERE posts.user_id = $1 LIMIT $2;
$ LANGUAGE sql;

Generated:

export interface Database {
  public: {
    Functions: {
      get_user_posts: {
        Args: {
          user_id: number
          limit_count?: number  // Has default
        }
        Returns: {
          id: number
          user_id: number
          content: string
        }[]
      }
    }
  }
}

Using Generated Types

With SDK

import { createClient } from '@postbase/sdk'
import type { Database } from './db/types'
 
const db = createClient<Database>({
  connectionString: process.env.DATABASE_URL,
})
 
// Fully typed queries
const users = await db.from('users').execute()
//    ^? User[]
 
const user = await db
  .from('users')
  .eq('id', 1)
  .single()
//    ^? User | null
 
// Type-safe inserts
await db.from('users').insert({
  email: 'alice@example.com',  // Required
  name: 'Alice',               // Required
  // created_at optional (has default)
})

Type Utilities

Extract types for use elsewhere:

import type { Database } from './db/types'
 
// Table row type
type User = Database['public']['Tables']['users']['Row']
 
// Insert type
type NewUser = Database['public']['Tables']['users']['Insert']
 
// Update type
type UserUpdate = Database['public']['Tables']['users']['Update']
 
// Function args
type GetUserPostsArgs = Database['public']['Functions']['get_user_posts']['Args']

Dates as Strings

For frameworks that serialize dates to JSON:

postbase types generate \
  --database myapp \
  --dates-as-strings \
  --output ./src/db/types.ts

{
  Row: {
    created_at: string  // Instead of Date
    updated_at: string
  }
}

Multi-Schema Support

Generate types for multiple schemas:

# Public schema
postbase types generate \
  --database myapp \
  --schema public \
  --output ./src/db/types/public.ts
 
# Auth schema
postbase types generate \
  --database myapp \
  --schema auth \
  --output ./src/db/types/auth.ts

Watch Mode

Regenerate types on schema changes:

# In package.json
{
  "scripts": {
    "db:types": "postbase types generate --database myapp --all --output ./src/db/types.ts",
    "db:types:watch": "nodemon --exec 'npm run db:types' --watch migrations"
  }
}

CI Integration

Generate types in CI to catch schema drift:

# .github/workflows/typecheck.yml
jobs:
  typecheck:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        
      - name: Generate types
        run: npm run db:types
        
      - name: Check for changes
        run: |
          if git diff --exit-code src/db/types.ts; then
            echo "Types up to date"
          else
            echo "Types out of date! Run 'npm run db:types'"
            exit 1
          fi

Best Practices

Commit Generated Types

Include types.ts in version control:

Ensures consistent types across team
Catches schema drift in code review
Works without database connection

Regenerate After Migrations

Add to your migration workflow:

postbase migrate up
postbase types generate --database myapp --all --output ./src/db/types.ts

Use Strict TypeScript

Enable strict mode for full type safety:

{
  "compilerOptions": {
    "strict": true,
    "noUncheckedIndexedAccess": true
  }
}