$ cat /posts/supabase-with-typescript-type-safe-database-queries.md

Supabase with TypeScript: Type-Safe Database Queries

drwxr-xr-x2026-01-265 min0 views

TypeScript integration with Supabase enables end-to-end type safety by automatically generating types from database schema, catching errors at compile time instead of runtime, providing autocomplete for table columns and relationships, and ensuring query results match expected structures preventing type mismatches throughout application lifecycle. Without TypeScript types, database queries return 'any' types requiring manual type assertions, missing typos in column names compile successfully but fail at runtime, changing database schemas silently break application code, and refactoring becomes risky without confidence that all database interactions are updated. This comprehensive guide covers generating TypeScript types from Supabase schema, using generated types with JavaScript client, creating type-safe queries for SELECT, INSERT, UPDATE operations, handling relationships and joins with types, building type-safe RPC functions, integrating with React components using proper types, automatic type inference for query results, custom type utilities for common patterns, and maintaining types as schema evolves. TypeScript becomes invaluable for production applications where database schema changes frequently, multiple developers collaborate requiring clear contracts, refactoring needs confidence, and runtime errors must be minimized. Before proceeding, understand JavaScript client basics, queries, and migrations.

Benefits of TypeScript with Supabase

Without TypeScript	With TypeScript	Benefit
data: any	data: Post[]	Autocomplete and validation
Typos discovered at runtime	Typos caught at compile time	Early error detection
Manual type assertions	Automatic type inference	Less boilerplate
Schema changes break silently	Type errors on schema changes	Safe refactoring
No IDE autocomplete	Full IntelliSense support	Better DX

Generating Database Types

bashgenerate_types.sh

# Install Supabase CLI
npm install -g supabase

# Login to Supabase
supabase login

# Generate types from your database schema
supabase gen types typescript --project-id "your-project-ref" > src/types/database.types.ts

# Or if linked to project
supabase gen types typescript --linked > src/types/database.types.ts

# Generated types include:
# - All table definitions
# - Column types
# - Relationships
# - Enum types
# - Function signatures

# Regenerate types after schema changes
supabase gen types typescript --linked > src/types/database.types.ts

# Add to package.json scripts
{
  "scripts": {
    "types:generate": "supabase gen types typescript --linked > src/types/database.types.ts"
  }
}

# Then run:
npm run types:generate

Type-Safe Supabase Client

typescriptsupabaseClient.ts

// src/types/database.types.ts (auto-generated)
export type Json =
  | string
  | number
  | boolean
  | null
  | { [key: string]: Json | undefined }
  | Json[]

export interface Database {
  public: {
    Tables: {
      posts: {
        Row: {
          id: string
          user_id: string
          title: string
          content: string | null
          published: boolean
          created_at: string
          updated_at: string
        }
        Insert: {
          id?: string
          user_id: string
          title: string
          content?: string | null
          published?: boolean
          created_at?: string
          updated_at?: string
        }
        Update: {
          id?: string
          user_id?: string
          title?: string
          content?: string | null
          published?: boolean
          created_at?: string
          updated_at?: string
        }
      }
      // Other tables...
    }
    Views: {
      // Database views
    }
    Functions: {
      // Database functions
    }
    Enums: {
      // Enum types
    }
  }
}

// src/lib/supabaseClient.ts
import { createClient } from '@supabase/supabase-js'
import type { Database } from '../types/database.types'

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!
const supabaseKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!

export const supabase = createClient<Database>(supabaseUrl, supabaseKey)

// Now all queries are type-safe!

Type-Safe CRUD Operations

typescripttyped_queries.ts

// Type-safe SELECT queries
import { supabase } from './lib/supabaseClient'
import type { Database } from './types/database.types'

type Post = Database['public']['Tables']['posts']['Row']

// Fetch all posts - TypeScript knows the return type
async function getAllPosts(): Promise<Post[]> {
  const { data, error } = await supabase
    .from('posts')
    .select('*')
  
  if (error) throw error
  
  // data is automatically typed as Post[]
  return data
}

// Fetch specific columns - type inference
async function getPostTitles() {
  const { data } = await supabase
    .from('posts')
    .select('id, title, created_at')
  
  // data is inferred as { id: string, title: string, created_at: string }[]
  return data
}

// Type-safe INSERT
type NewPost = Database['public']['Tables']['posts']['Insert']

async function createPost(post: NewPost) {
  const { data, error } = await supabase
    .from('posts')
    .insert(post)
    .select()
    .single()
  
  if (error) throw error
  return data  // Typed as Post
}

// Usage with autocomplete
const newPost: NewPost = {
  title: 'My Post',
  content: 'Post content',
  user_id: 'user-123',
  // published is optional (has default)
  // id is optional (auto-generated)
  // timestamps are optional (auto-generated)
}

await createPost(newPost)

// Type-safe UPDATE
type PostUpdate = Database['public']['Tables']['posts']['Update']

async function updatePost(id: string, updates: PostUpdate) {
  const { data, error } = await supabase
    .from('posts')
    .update(updates)
    .eq('id', id)
    .select()
    .single()
  
  if (error) throw error
  return data
}

// Usage
await updatePost('post-123', {
  title: 'Updated Title',
  published: true
  // All fields are optional
})

// Type-safe DELETE
async function deletePost(id: string) {
  const { error } = await supabase
    .from('posts')
    .delete()
    .eq('id', id)
  
  if (error) throw error
}

Type-Safe Relationships

typescriptrelationships.ts

// Type-safe joins and relationships
import { supabase } from './lib/supabaseClient'
import type { Database } from './types/database.types'

type Post = Database['public']['Tables']['posts']['Row']
type Comment = Database['public']['Tables']['comments']['Row']

// Define combined type for post with comments
type PostWithComments = Post & {
  comments: Comment[]
}

async function getPostWithComments(postId: string): Promise<PostWithComments | null> {
  const { data, error } = await supabase
    .from('posts')
    .select(`
      *,
      comments (*)
    `)
    .eq('id', postId)
    .single()
  
  if (error) throw error
  
  // TypeScript knows data has comments array
  return data
}

// Usage with full type safety
const post = await getPostWithComments('post-123')
if (post) {
  console.log(post.title)  // ✓ string
  console.log(post.comments.length)  // ✓ number
  post.comments.forEach(comment => {
    console.log(comment.content)  // ✓ Autocomplete works!
  })
}

// Nested relationships
type PostWithAuthorAndComments = Post & {
  author: Database['public']['Tables']['profiles']['Row']
  comments: (Comment & {
    author: Database['public']['Tables']['profiles']['Row']
  })[]
}

async function getPostWithFullDetails(
  postId: string
): Promise<PostWithAuthorAndComments | null> {
  const { data, error } = await supabase
    .from('posts')
    .select(`
      *,
      author:profiles!user_id (*),
      comments (
        *,
        author:profiles!user_id (*)
      )
    `)
    .eq('id', postId)
    .single()
  
  if (error) throw error
  return data
}

Type-Safe RPC Functions

typescriptrpc_functions.ts

// Database function definition (SQL)
-- create function search_posts(search_query text)
-- returns table (
--   id uuid,
--   title text,
--   content text,
--   rank real
-- )
-- ...

// After generating types, use in TypeScript
import { supabase } from './lib/supabaseClient'

// Define return type based on function
interface SearchResult {
  id: string
  title: string
  content: string
  rank: number
}

async function searchPosts(query: string): Promise<SearchResult[]> {
  const { data, error } = await supabase
    .rpc('search_posts', {
      search_query: query
    })
  
  if (error) throw error
  
  // Cast to our defined type
  return data as SearchResult[]
}

// Usage
const results = await searchPosts('typescript')
results.forEach(result => {
  console.log(result.title)  // ✓ Type-safe
  console.log(result.rank)   // ✓ Autocomplete
})

// Type-safe function parameters
async function incrementPostViews(postId: string): Promise<void> {
  const { error } = await supabase
    .rpc('increment_post_views', {
      post_id: postId  // Parameter names are type-checked
    })
  
  if (error) throw error
}

TypeScript with React Components

typescriptPostsList.tsx

// PostsList.tsx - Type-safe React component
import { useState, useEffect } from 'react'
import { supabase } from './lib/supabaseClient'
import type { Database } from './types/database.types'

type Post = Database['public']['Tables']['posts']['Row']

function PostsList() {
  const [posts, setPosts] = useState<Post[]>([])
  const [loading, setLoading] = useState(true)
  const [error, setError] = useState<string | null>(null)

  useEffect(() => {
    fetchPosts()
  }, [])

  async function fetchPosts() {
    try {
      const { data, error } = await supabase
        .from('posts')
        .select('*')
        .order('created_at', { ascending: false })
      
      if (error) throw error
      
      // TypeScript ensures data matches Post[] type
      setPosts(data || [])
    } catch (err) {
      setError(err instanceof Error ? err.message : 'Unknown error')
    } finally {
      setLoading(false)
    }
  }

  if (loading) return <div>Loading...</div>
  if (error) return <div>Error: {error}</div>

  return (
    <div>
      {posts.map(post => (
        <PostCard key={post.id} post={post} />
      ))}
    </div>
  )
}

// PostCard.tsx - Type-safe component with props
interface PostCardProps {
  post: Post
}

function PostCard({ post }: PostCardProps) {
  return (
    <div className="post-card">
      <h2>{post.title}</h2>
      <p>{post.content}</p>
      <time>{new Date(post.created_at).toLocaleDateString()}</time>
    </div>
  )
}

export default PostsList

Type-Safe Custom Hooks

typescriptuseSupabaseQuery.ts

// hooks/useSupabaseQuery.ts - Generic type-safe hook
import { useState, useEffect } from 'react'
import { supabase } from '../lib/supabaseClient'
import type { Database } from '../types/database.types'

type Tables = Database['public']['Tables']
type TableName = keyof Tables

interface UseQueryResult<T> {
  data: T[] | null
  loading: boolean
  error: Error | null
  refetch: () => Promise<void>
}

function useSupabaseQuery<T extends TableName>(
  table: T,
  query?: (builder: any) => any
): UseQueryResult<Tables[T]['Row']> {
  const [data, setData] = useState<Tables[T]['Row'][] | null>(null)
  const [loading, setLoading] = useState(true)
  const [error, setError] = useState<Error | null>(null)

  async function fetchData() {
    try {
      setLoading(true)
      let queryBuilder = supabase.from(table).select('*')
      
      if (query) {
        queryBuilder = query(queryBuilder)
      }
      
      const { data: result, error: queryError } = await queryBuilder
      
      if (queryError) throw queryError
      
      setData(result)
    } catch (err) {
      setError(err instanceof Error ? err : new Error('Unknown error'))
    } finally {
      setLoading(false)
    }
  }

  useEffect(() => {
    fetchData()
  }, [table])

  return { data, loading, error, refetch: fetchData }
}

// Usage in components
function MyComponent() {
  const { data: posts, loading } = useSupabaseQuery('posts', (query) =>
    query.eq('published', true).order('created_at', { ascending: false })
  )

  if (loading) return <div>Loading...</div>

  return (
    <div>
      {posts?.map(post => (
        <div key={post.id}>
          <h3>{post.title}</h3>
        </div>
      ))}
    </div>
  )
}

Type Utilities and Helpers

typescripttype_helpers.ts

// types/helpers.ts - Reusable type utilities
import type { Database } from './database.types'

// Extract table types
export type Tables<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Row']

export type Insertable<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Insert']

export type Updateable<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Update']

// Usage
import type { Tables, Insertable, Updateable } from './types/helpers'

type Post = Tables<'posts'>
type NewPost = Insertable<'posts'>
type PostUpdate = Updateable<'posts'>

// Enum helper
export type Enums<T extends keyof Database['public']['Enums']> =
  Database['public']['Enums'][T]

// Example enum usage
type PostStatus = Enums<'post_status'>

// Partial update type
export type PartialUpdate<T> = {
  [P in keyof T]?: T[P] | null
}

// API response wrapper
export interface ApiResponse<T> {
  data: T | null
  error: Error | null
  loading: boolean
}

// Paginated response
export interface PaginatedResponse<T> {
  data: T[]
  count: number
  page: number
  pageSize: number
  totalPages: number
}

TypeScript Best Practices

Regenerate Types After Schema Changes: Run type generation after every migration to keep types in sync
Use Strict TypeScript Config: Enable strict mode in tsconfig.json for maximum type safety
Create Type Aliases: Extract commonly used types into reusable aliases avoiding duplication
Type Component Props: Always type React component props for better refactoring and autocomplete
Handle Nullable Fields: Database fields can be null; handle these cases in TypeScript
Version Control Types: Commit generated types to version control so team members have consistent types
Use Enums for Constants: Define database enums as TypeScript types for type-safe constant values

Pro Tip: Set up a Git pre-commit hook to automatically regenerate types ensuring they're always in sync with your database schema. Combine with migrations workflow for complete type safety throughout development.

Common Issues

Type Generation Fails: Ensure Supabase CLI is updated and you're logged in with supabase login
Types Out of Sync: Regenerate types after every schema change or migration
Incorrect Relationship Types: Manually define types for complex joins not auto-generated
RPC Function Types Missing: Manually define return types for database functions

Next Steps

Build Complete Apps: Apply TypeScript in React projects or Next.js applications
Add Pagination: Implement type-safe pagination with proper types
Manage Schema: Use migrations to track schema changes and regenerate types
Secure Access: Combine with Row Level Security for production-ready apps

Conclusion

TypeScript integration with Supabase provides end-to-end type safety by automatically generating types from database schema, enabling autocomplete for queries, catching errors at compile time, and ensuring code remains synchronized with database structure throughout development. By generating types with Supabase CLI after schema changes, creating type-safe Supabase client with Database generic, using Row types for queries and Insert/Update types for mutations, defining combined types for relationships and joins, and building reusable type utilities for common patterns, you eliminate entire categories of runtime errors. TypeScript catches typos in column names immediately, provides autocomplete for all database fields, ensures query results match expected types, validates function parameters at compile time, and makes refactoring safe when schema evolves. Always regenerate types after migrations, enable strict TypeScript configuration for maximum safety, create type aliases for frequently used types, properly type React component props, handle nullable database fields correctly, and version control generated types for team consistency. TypeScript becomes essential for production Supabase applications where multiple developers collaborate, schema changes frequently, refactoring must be confident, and runtime errors should be minimized. Continue building type-safe applications with Next.js integration, migration workflows, and pagination features.