Database Introspection

Kysely’s DatabaseIntrospector provides runtime access to database metadata. This is useful for building dynamic queries, generating documentation, or implementing database management tools.

Accessing the Introspector

The introspector is available on the Kysely instance:

const introspector = db.introspection

Getting Schemas

Retrieve all schemas in the database (PostgreSQL, MSSQL):

const schemas = await db.introspection.getSchemas()

schemas.forEach((schema) => {
  console.log(schema.name)
})

Example output:

[
  { name: 'public' },
  { name: 'auth' },
  { name: 'app' }
]

Getting Tables

Retrieve metadata for all tables and views:

const tables = await db.introspection.getTables()

tables.forEach((table) => {
  console.log(`${table.name} (${table.isView ? 'view' : 'table'})`)
  
  table.columns.forEach((column) => {
    console.log(`  - ${column.name}: ${column.dataType}`)
  })
})

Including Internal Tables

By default, Kysely’s internal migration tables are excluded. To include them:

const tables = await db.introspection.getTables({
  withInternalKyselyTables: true
})

Table Metadata

Each table object contains:

interface TableMetadata {
  name: string          // Table name
  isView: boolean       // true for views, false for tables
  schema?: string       // Schema name (PostgreSQL, MSSQL)
  columns: ColumnMetadata[]
}

Example:

const tables = await db.introspection.getTables()
const personTable = tables.find(t => t.name === 'person')

if (personTable) {
  console.log(`Table: ${personTable.name}`)
  console.log(`Schema: ${personTable.schema ?? 'default'}`)
  console.log(`Is View: ${personTable.isView}`)
  console.log(`Columns: ${personTable.columns.length}`)
}

Column Metadata

Each column object provides detailed information:

interface ColumnMetadata {
  name: string              // Column name
  dataType: string          // Database-specific data type
  dataTypeSchema?: string   // Schema where the data type is defined
  isAutoIncrementing: boolean
  isNullable: boolean
  hasDefaultValue: boolean
  comment?: string          // Column comment if available
}

Data Type Variations

The dataType field returns the database’s native type name, which varies across dialects even for the same migration.

Example: An integer column definition produces:

PostgreSQL: int4
MySQL: int
SQLite: INTEGER

const tables = await db.introspection.getTables()
const personTable = tables.find(t => t.name === 'person')
const idColumn = personTable?.columns.find(c => c.name === 'id')

if (idColumn) {
  console.log(`Type: ${idColumn.dataType}`)  // e.g., "int4" on PostgreSQL
  console.log(`Nullable: ${idColumn.isNullable}`)
  console.log(`Auto-increment: ${idColumn.isAutoIncrementing}`)
  console.log(`Has default: ${idColumn.hasDefaultValue}`)
}

Practical Examples

Generating Table Documentation

async function generateDatabaseDocs(db: Kysely<any>) {
  const tables = await db.introspection.getTables()
  
  for (const table of tables) {
    if (table.isView) continue
    
    console.log(`\n## ${table.name}`)
    if (table.schema) {
      console.log(`Schema: ${table.schema}`)
    }
    
    console.log('\nColumns:')
    table.columns.forEach((col) => {
      const nullable = col.isNullable ? 'NULL' : 'NOT NULL'
      const autoInc = col.isAutoIncrementing ? ' AUTO_INCREMENT' : ''
      const defaultVal = col.hasDefaultValue ? ' DEFAULT' : ''
      
      console.log(
        `- ${col.name}: ${col.dataType} ${nullable}${autoInc}${defaultVal}`
      )
      
      if (col.comment) {
        console.log(`  ${col.comment}`)
      }
    })
  }
}

await generateDatabaseDocs(db)

Validating Expected Schema

async function validateSchema(db: Kysely<any>) {
  const tables = await db.introspection.getTables()
  
  const requiredTables = ['person', 'post', 'comment']
  const missingTables = requiredTables.filter(
    name => !tables.find(t => t.name === name)
  )
  
  if (missingTables.length > 0) {
    throw new Error(`Missing tables: ${missingTables.join(', ')}`)
  }
  
  // Validate person table structure
  const personTable = tables.find(t => t.name === 'person')
  const requiredColumns = ['id', 'email', 'created_at']
  
  const missingColumns = requiredColumns.filter(
    name => !personTable?.columns.find(c => c.name === name)
  )
  
  if (missingColumns.length > 0) {
    throw new Error(
      `person table missing columns: ${missingColumns.join(', ')}`
    )
  }
  
  console.log('✓ Schema validation passed')
}

await validateSchema(db)

Finding Tables by Pattern

async function findTablesByPattern(
  db: Kysely<any>,
  pattern: RegExp
): Promise<TableMetadata[]> {
  const tables = await db.introspection.getTables()
  return tables.filter(table => pattern.test(table.name))
}

// Find all audit tables
const auditTables = await findTablesByPattern(db, /_audit$/)
auditTables.forEach(t => console.log(t.name))

Detecting Schema Changes

interface SchemaSnapshot {
  tables: Record<string, string[]>  // table name -> column names
}

async function captureSchemaSnapshot(db: Kysely<any>): Promise<SchemaSnapshot> {
  const tables = await db.introspection.getTables()
  
  return {
    tables: Object.fromEntries(
      tables.map(t => [
        t.name,
        t.columns.map(c => c.name)
      ])
    )
  }
}

async function compareSchemas(
  db: Kysely<any>,
  oldSnapshot: SchemaSnapshot
) {
  const newSnapshot = await captureSchemaSnapshot(db)
  
  // Find new tables
  const newTables = Object.keys(newSnapshot.tables).filter(
    name => !oldSnapshot.tables[name]
  )
  
  // Find dropped tables
  const droppedTables = Object.keys(oldSnapshot.tables).filter(
    name => !newSnapshot.tables[name]
  )
  
  // Find modified tables
  const commonTables = Object.keys(newSnapshot.tables).filter(
    name => oldSnapshot.tables[name]
  )
  
  for (const tableName of commonTables) {
    const oldCols = oldSnapshot.tables[tableName]
    const newCols = newSnapshot.tables[tableName]
    
    const addedCols = newCols.filter(c => !oldCols.includes(c))
    const droppedCols = oldCols.filter(c => !newCols.includes(c))
    
    if (addedCols.length > 0 || droppedCols.length > 0) {
      console.log(`${tableName}:`)
      if (addedCols.length) console.log(`  + ${addedCols.join(', ')}`)
      if (droppedCols.length) console.log(`  - ${droppedCols.join(', ')}`)
    }
  }
  
  if (newTables.length) console.log(`New tables: ${newTables.join(', ')}`)
  if (droppedTables.length) console.log(`Dropped: ${droppedTables.join(', ')}`)
}

Building a Dynamic Query Builder

async function buildSelectAllQuery(
  db: Kysely<any>,
  tableName: string
) {
  const tables = await db.introspection.getTables()
  const table = tables.find(t => t.name === tableName)
  
  if (!table) {
    throw new Error(`Table ${tableName} not found`)
  }
  
  // Only select non-binary columns
  const columns = table.columns
    .filter(c => !c.dataType.includes('blob') && !c.dataType.includes('bytea'))
    .map(c => c.name)
  
  return db
    .selectFrom(tableName as any)
    .select(columns as any)
}

const query = await buildSelectAllQuery(db, 'person')
const results = await query.execute()

Legacy Method

The getMetadata() method is deprecated. Use getTables() instead.

// Deprecated
const metadata = await db.introspection.getMetadata()
const tables = metadata.tables

// Use this instead
const tables = await db.introspection.getTables()

Use Cases

Database Documentation - Auto-generate schema documentation
Schema Validation - Verify database structure in tests
Migration Safety - Check schema before applying migrations
Dynamic UIs - Build admin interfaces that adapt to schema changes
Code Generation - Generate TypeScript types from database schema
Monitoring - Track schema changes over time
Multi-tenancy - Introspect tenant-specific schemas

Performance Considerations

Introspection queries can be slow on large databases
Cache results when possible instead of repeatedly calling introspection methods
Filter results early if you only need specific tables

// Cache introspection results
let cachedTables: TableMetadata[] | null = null

async function getCachedTables(db: Kysely<any>) {
  if (!cachedTables) {
    cachedTables = await db.introspection.getTables()
  }
  return cachedTables
}

Get Started

Core Concepts

Query Operations

Schema & Migrations

Recipes & Guides

Database Introspection

Accessing the Introspector

Getting Schemas

Getting Tables

Including Internal Tables

Table Metadata

Column Metadata

Data Type Variations

Practical Examples

Generating Table Documentation

Validating Expected Schema

Finding Tables by Pattern

Detecting Schema Changes

Building a Dynamic Query Builder

Legacy Method

Use Cases

Performance Considerations

​Accessing the Introspector

​Getting Schemas

​Getting Tables

​Including Internal Tables

​Table Metadata

​Column Metadata

​Data Type Variations

​Practical Examples

​Generating Table Documentation

​Validating Expected Schema

​Finding Tables by Pattern

​Detecting Schema Changes

​Building a Dynamic Query Builder

​Legacy Method

​Use Cases

​Performance Considerations

Accessing the Introspector

Getting Schemas

Getting Tables

Including Internal Tables

Table Metadata

Column Metadata

Data Type Variations

Practical Examples

Generating Table Documentation

Validating Expected Schema

Finding Tables by Pattern

Detecting Schema Changes

Building a Dynamic Query Builder

Legacy Method

Use Cases

Performance Considerations