Database Introspection

Overview

Database introspection in Drizzle Kit recovers relational database schemas from live database instances across multiple supported dialects, normalizing catalog metadata into structured JSON migration snapshots and translating them into idiomatic Drizzle TypeScript schema definitions.

Sources: drizzle-kit/src/cli/schema.ts:538-623, drizzle-kit/src/serializer/singlestoreSerializer.ts:400-411, and drizzle-kit/src/serializer/mysqlSerializer.ts:564-573

CLI Introspection Orchestration

Dialect Dispatch and Driver Checks

The CLI command handler for introspection ensures output directories are created recursively via mkdirSync(out, { recursive: true }) and evaluates dialect branches to dynamically import and invoke dialect-specific introspection routines. For PostgreSQL credentials carrying a specific driver option, version compatibility checks against drizzle-orm are enforced before proceeding.

Sources: drizzle-kit/src/cli/schema.ts:524-625

Filtering and Progress Reporting

Table filtering is implemented across database dialects by mapping each provided table filter string into a Minimatch instance. The filter function evaluates table names against all matchers, taking negation patterns into account and enforcing that all match criteria pass via flags.every(Boolean).

Copyconst matchers = tablesFilter.map((it) => {
	return new Minimatch(it);
});
 
const filter = (tableName: string) => {
	if (matchers.length === 0) return true;
 
	let flags: boolean[] = [];
 
	for (let matcher of matchers) {
		if (matcher.negate) {
			if (!matcher.match(tableName)) {
				flags.push(false);
			}
		}
 
		if (matcher.match(tableName)) {
			flags.push(true);
		}
	}
 
	if (flags.length > 0) {
		return flags.every(Boolean);
	}
	return false;
};

Progress reporting runs concurrently with database extraction by instantiating an IntrospectProgress reporter and wrapping the database fetch task with renderWithTask, where status updates propagate stage, count, and status arguments down to progress.update(stage, count, status).

Sources: drizzle-kit/src/cli/commands/introspect.ts:184-234, drizzle-kit/src/cli/commands/introspect.ts:318-351

Snapshot Diffing and Output Generation

Once schema objects are retrieved from the database and converted into TypeScript files (schema.ts and relations.ts), migration folders are inspected via prepareOutFolder(out, dialect). If no existing snapshots are detected (snapshots.length === 0), dialect-specific snapshot diff functions compute SQL migration statements and metadata.

Copyconst { snapshots, journal } = prepareOutFolder(out, 'mysql');
 
if (snapshots.length === 0) {
	const { sqlStatements, _meta } = await applyMysqlSnapshotsDiff(
		squashMysqlScheme(dryMySql),
		squashMysqlScheme(schema),
		tablesResolver,
		columnsResolver,
		mySqlViewsResolver,
		dryMySql,
		schema,
	);
 
	writeResult({
		cur: schema,
		sqlStatements,
		journal,
		_meta,
		outFolder: out,
		breakpoints,
		type: 'introspect',
		prefixMode: prefix,
	});
} else {
	render(
		`[${
			chalk.blue(
				'i',
			)
		}] No SQL generated, you already have migrations in project`,
	);
}

Sources: drizzle-kit/src/cli/commands/introspect.ts:364-395

PostgreSQL Catalog Inspection

PostgreSQL Introspection and Push Execution

PostgreSQL schema introspection is driven through pgPushIntrospect, which processes user-supplied table filter strings into Minimatch instances to evaluate and filter table names against positive and negative match criteria.

Copyconst matchers = filters.map((it) => {
	return new Minimatch(it);
});
 
const filter = (tableName: string) => {
	if (matchers.length === 0) return true;
 
	let flags: boolean[] = [];
 
	for (let matcher of matchers) {
		if (matcher.negate) {
			if (!matcher.match(tableName)) {
				flags.push(false);
			}
		}
 
		if (matcher.match(tableName)) {
			flags.push(true);
		}
	}
 
	if (flags.length > 0) {
		return flags.every(Boolean);
	}
	return false;
};

Sources: drizzle-kit/src/cli/commands/pgIntrospect.ts:17-42

The introspection execution wraps schema pulling within a terminal progress view using renderWithTask before appending internal identifiers to construct the final serialized PgSchema payload.

Copy	const progress = new ProgressView(
		'Pulling schema from database...',
		'Pulling schema from database...',
	);
	const res = await renderWithTask(
		progress,
		fromDatabase(db, filter, schemaFilters, entities, undefined, tsSchema),
	);
 
	const schema = { id: originUUID, prevId: '', ...res } as PgSchema;
	const { internal, ...schemaWithoutInternals } = schema;
	return { schema: schemaWithoutInternals };

Sources: drizzle-kit/src/cli/commands/pgIntrospect.ts:43-54

Catalog Transformation and TypeScript Generation

Catalog reconstruction inspects database entities, mapping identities, sequences, columns, roles, and schema definitions into corresponding TypeScript constructs. When reconstructing column identities, parameters such as maxValue, cache, and cycle are serialized into .generatedAlwaysAsIdentity() or .generatedByDefaultAsIdentity() calls.

Copy	if (identity?.maxValue) {
		paramsObj += `, maxValue: ${identity.maxValue}`;
	}
	if (identity?.cache) {
		paramsObj += `, cache: ${identity.cache}`;
	}
	if (identity?.cycle) {
		paramsObj += `, cycle: true`;
	}
	paramsObj += ' }';
	if (identity?.type === 'always') {
		return `.generatedAlwaysAsIdentity(${paramsObj})`;
	}
	return `.generatedByDefaultAsIdentity(${paramsObj})`;

Sources: drizzle-kit/src/introspect-pg.ts:289-302

Sources: drizzle-kit/src/introspect-pg.ts:304-307

MySQL and SingleStore Schema Recovery

Introspection Commands and Table Filtering

MySQL and SingleStore schema introspection processes database metadata by executing database pulls through mysqlPushIntrospect and singlestorePushIntrospect. Both routines accept database connection handles, target database names, and filter arrays to construct Minimatch instances that selectively include or exclude tables based on pattern matching rules and negated match flags.

Copy	const matchers = filters.map((it) => {
		return new Minimatch(it);
	});
 
	const filter = (tableName: string) => {
		if (matchers.length === 0) return true;
 
		let flags: boolean[] = [];
 
		for (let matcher of matchers) {
			if (matcher.negate) {
				if (!matcher.match(tableName)) {
					flags.push(false);
				}
			}
 
			if (matcher.match(tableName)) {
				flags.push(true);
			}
		}
 
		if (flags.length > 0) {
			return flags.every(Boolean);
		}
		return false;
	};

Sources: drizzle-kit/src/cli/commands/mysqlIntrospect.ts:14-39, drizzle-kit/src/cli/commands/singlestoreIntrospect.ts:14-39

Sources: drizzle-kit/src/cli/commands/mysqlIntrospect.ts:41-48, drizzle-kit/src/cli/commands/singlestoreIntrospect.ts:41-48

Casing Transformation and Schema Squashing

Field names and identifiers undergo casing transformation determined by the active casing configuration. The prepareCasing helper checks whether casing is set to 'preserve' or 'camel', routing column keys through escapeColumnKey or string transformation methods.

Copyconst escapeColumnKey = (value: string) => {
	if (/^(?![a-zA-Z_$][a-zA-Z0-9_$]*$).+$/.test(value)) {
		return `"${value}"`;
	}
	return value;
};
 
const prepareCasing = (casing?: Casing) => (value: string) => {
	if (casing === 'preserve') {
		return escapeColumnKey(value);
	}
	if (casing === 'camel') {
		return escapeColumnKey(value.camelCase());
	}
 
	assertUnreachable(casing);
};

Sources: drizzle-kit/src/introspect-mysql.ts:104-120, drizzle-kit/src/introspect-singlestore.ts:110-126

Catalog serialization and migration diffing rely on squasher functions defined in mysqlSchema.ts. The squasher flattens indices, foreign keys, primary keys, unique constraints, check constraints, and views into delimited string representations for snapshot comparison.

Sources: drizzle-kit/src/serializer/mysqlSchema.ts:223-292

Sources: drizzle-kit/src/introspect-mysql.ts:602-609

SQLite Catalog Recovery

Table Filtering and Introspection Execution

The SQLite introspection pipeline connects to live SQLite and LibSQL databases using configured credentials, applying glob table filters via Minimatch before delegating schema serialization to fromDatabase. The sqliteIntrospect and sqlitePushIntrospect functions process filter matchers supporting negation rules, evaluating table names through an array accumulator and returning boolean conditions.

Copyexport const sqliteIntrospect = async (
	credentials: SqliteCredentials,
	filters: string[],
	casing: Casing,
) => {
	const { connectToSQLite } = await import('../connections');
	const db = await connectToSQLite(credentials);
 
	const matchers = filters.map((it) => {
		return new Minimatch(it);
	});
 
	const filter = (tableName: string) => {
		if (matchers.length === 0) return true;
 
		let flags: boolean[] = [];
 
		for (let matcher of matchers) {
			if (matcher.negate) {
				if (!matcher.match(tableName)) {
					flags.push(false);
				}
			}
 
			if (matcher.match(tableName)) {
				flags.push(true);
			}
		}
 
		if (flags.length > 0) {
			return flags.every(Boolean);
		}
		return false;
	};
 
	const progress = new IntrospectProgress();
	const res = await renderWithTask(
		progress,
		fromDatabase(db, filter, (stage, count, status) => {
			progress.update(stage, count, status);
		}),
	);
 
	const schema = { id: originUUID, prevId: '', ...res } as SQLiteSchema;
	const ts = schemaToTypeScript(schema, casing);
	return { schema, ts };
};

Sources: drizzle-kit/src/cli/commands/sqliteIntrospect.ts:12-58

Sources: drizzle-kit/src/cli/commands/sqliteIntrospect.ts:88-92

Column Mapping and Default Value Parsing

Recovered SQLite column types map database definitions to TypeScript declarations while processing default values, column flags, and self-referential or cyclic foreign key constraints. The mapColumnDefault utility handles SQL expressions enclosed in parentheses, stringified NULL constants, and quoted string defaults by unescaping interior quotes.

Copyconst mapColumnDefault = (defaultValue: any) => {
	if (
		typeof defaultValue === 'string'
		&& defaultValue.startsWith('(')
		&& defaultValue.endsWith(')')
	) {
		return `sql\`${defaultValue}\``;
	}
	if (defaultValue === 'NULL') {
		return `sql\`NULL\``;
	}
 
	if (
		typeof defaultValue === 'string'
	) {
		return defaultValue.substring(1, defaultValue.length - 1).replaceAll('"', '\\"').replaceAll("''", "'");
	}
 
	return defaultValue;
};

Sources: drizzle-kit/src/introspect-sqlite.ts:258-278

Sources: drizzle-kit/src/introspect-sqlite.ts:266-269

Foreign key relationships are analyzed for self-referential structures and cyclic dependencies by checking key pairs against registered relation sets.

Sources: drizzle-kit/src/introspect-sqlite.ts:248-256

Gel Schema Extraction

Default Value Mapping Rules

Gel introspection maps database types, array flags, and default expression constraints into structured TypeScript declarations Migration Toolkit Schema. The mapDefault function inspects column metadata, handling type-specific formatting for integers, floating-point numbers, UUIDs, dates, durations, JSON, and byte arrays.

Copyconst isCyclic = (fk: ForeignKey) => {
	const key = `${fk.tableFrom}-${fk.tableTo}`;
	const reverse = `${fk.tableTo}-${fk.tableFrom}`;
	return relations.has(key) && relations.has(reverse);
};
 
const isSelf = (fk: ForeignKey) => {
	return fk.tableFrom === fk.tableTo;
};

Sources: drizzle-kit/src/introspect-gel.ts:522-530

Array defaults undergo sanitization through buildArrayDefault, checking whether the default value string begins with ARRAY[ or _nullif_array_nulls(ARRAY[ before wrapping expressions in SQL template literals.

Copyconst buildArrayDefault = (defaultValue: string, typeName: string): string => {
	if (
		typeof defaultValue === 'string'
		&& !(defaultValue.startsWith('_nullif_array_nulls(ARRAY[') || defaultValue.startsWith('ARRAY['))
	) {
		return `sql\`${defaultValue}\``;
	}
 
	const regex = /ARRAY\[(.*)\]/;
	const match = defaultValue.match(regex);
 
	if (!match) {
		return `sql\`${defaultValue}\``;
	}
 
	defaultValue = match[1];
	return `sql\`[${defaultValue}]\``;
};

Sources: drizzle-kit/src/introspect-gel.ts:532-549

Sources: drizzle-kit/src/introspect-gel.ts:596-673

Sources: drizzle-kit/src/introspect-gel.ts:564-663

Sources: drizzle-kit/src/introspect-gel.ts:578-594

TypeScript Declaration Code Generation

Schema to TypeScript Mapping and Imports

The code generation phase inspects recovered schema structures and aggregates column types, constraints, and relational entities to emit standard Drizzle ORM TypeScript declarations. Across PostgreSQL, MySQL, SingleStore, SQLite, and Gel introspectors, builders verify dependency names and compile distinct import lists. For instance, SingleStore and MySQL generation evaluate table column types, composite primary keys, unique constraints, and indexes to construct the required core importsMigration Toolkit Schema. Casing configurations (preserve or camel) determine whether identifiers undergo key escaping via prepareCasing and escapeColumnKey.

Copyconst singlestoreImportsList = new Set([
	'singlestoreTable',
	'singlestoreEnum',
	'bigint',
	'binary',
	'boolean',
	'char',
	'date',
	'datetime',
	'decimal',
	'double',
	'float',
	'int',
	'json',
	// TODO: add new type BSON
	// TODO: add new type Blob
	// TODO: add new type UUID
	// TODO: add new type GUID
	// TODO: add new type Vector
	// TODO: add new type GeoPoint
	'mediumint',
	'real',
	'serial',
	'smallint',
	'text',
	'tinytext',
	'mediumtext',
	'longtext',
	'time',
	'timestamp',
	'tinyint',
	'varbinary',
	'varchar',
	'vector',
	'year',
	'enum',
]);

Sources: drizzle-kit/src/introspect-singlestore.ts:19-55

Sources: drizzle-kit/src/introspect-singlestore.ts:110-115, drizzle-kit/src/introspect-mysql.ts:104-109

Column Mapping and Default Value Formatting

Column generators translate raw data types and constraint flags into chainable Drizzle builder methods. Unsigned integer modifiers, auto-increment configurations, and default expressions are formatted through dialect-specific helper functions such as mapColumnDefault, mapColumnDefaultForJson, and binaryConfig.

Copyconst mapColumnDefault = (defaultValue: any, isExpression?: boolean) => {
	if (isExpression) {
		return `sql\`${defaultValue}\``;
	}
 
	return defaultValue;
};
 
const mapColumnDefaultForJson = (defaultValue: any) => {
	if (
		typeof defaultValue === 'string'
		&& defaultValue.startsWith("('")
		&& defaultValue.endsWith("')")
	) {
		return defaultValue.substring(2, defaultValue.length - 2);
	}
 
	return defaultValue;
};

Sources: drizzle-kit/src/introspect-singlestore.ts:341-359

Sources: drizzle-kit/src/introspect-singlestore.ts:341-388

Sources: drizzle-kit/src/introspect-pg.ts:333-335, drizzle-kit/src/introspect-mysql.ts:156-160

Related

• Migration Toolkit Schema
• CLI Command Architecture