Database Introspection

Inspect database metadata including tables, columns, and schemas at runtime

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

1. Database Documentation - Auto-generate schema documentation
2. Schema Validation - Verify database structure in tests
3. Migration Safety - Check schema before applying migrations
4. Dynamic UIs - Build admin interfaces that adapt to schema changes
5. Code Generation - Generate TypeScript types from database schema
6. Monitoring - Track schema changes over time
7. 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
}