schema.ts•7.24 kB
/**
* Schema utilities for Google Cloud Spanner
*/
import { SpannerSchema, SpannerTable, getSpannerClient } from './types.js';
/**
* Extracts schema information from Spanner database
*
* @param instanceId Spanner instance ID
* @param databaseId Spanner database ID
* @returns Schema information for the database
*/
export async function getSpannerSchema(instanceId: string, databaseId: string): Promise<SpannerSchema> {
const spanner = await getSpannerClient();
console.log(`Using Spanner client with project ID: ${spanner.projectId} for getSpannerSchema`);
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);
// Query for tables
const [tablesResult] = await database.run({
sql: `SELECT t.table_name
FROM information_schema.tables t
WHERE t.table_catalog = '' AND t.table_schema = ''
ORDER BY t.table_name`
});
if (!tablesResult || tablesResult.length === 0) {
return { tables: [] };
}
const tables: SpannerTable[] = [];
// Process each table to get columns
for (const tableRow of tablesResult) {
const tableName = (tableRow as any).table_name as string;
// Get columns for this table
const [columnsResult] = await database.run({
sql: `SELECT column_name, spanner_type, is_nullable
FROM information_schema.columns
WHERE table_catalog = '' AND table_schema = '' AND table_name = @tableName
ORDER BY ordinal_position`,
params: { tableName }
});
const columns = columnsResult.map((col: any) => ({
name: col.column_name as string,
type: col.spanner_type as string,
nullable: col.is_nullable === 'YES'
}));
// Get indexes for this table
const [indexesResult] = await database.run({
sql: `SELECT i.index_name, ic.column_name, i.is_unique
FROM information_schema.indexes i
JOIN information_schema.index_columns ic
ON i.table_catalog = ic.table_catalog
AND i.table_schema = ic.table_schema
AND i.table_name = ic.table_name
AND i.index_name = ic.index_name
WHERE i.table_catalog = '' AND i.table_schema = '' AND i.table_name = @tableName
ORDER BY i.index_name, ic.ordinal_position`,
params: { tableName }
});
// Group index columns by index name
const indexMap = new Map<string, { name: string; columns: string[]; unique: boolean }>();
for (const idx of indexesResult) {
const indexName = (idx as any).index_name as string;
const columnName = (idx as any).column_name as string;
const isUnique = (idx as any).is_unique as boolean;
if (!indexMap.has(indexName)) {
indexMap.set(indexName, { name: indexName, columns: [], unique: isUnique });
}
indexMap.get(indexName)?.columns.push(columnName);
}
const indexes = Array.from(indexMap.values());
// Get foreign keys for this table (if available)
let foreignKeys: { name: string; columns: string[]; referencedTable: string; referencedColumns: string[] }[] = [];
try {
// Note: This query might fail in some Spanner databases that don't have the referential_constraints view
const [foreignKeysResult] = await database.run({
sql: `SELECT
rc.constraint_name,
kcu1.column_name,
kcu2.table_name AS referenced_table,
kcu2.column_name AS referenced_column
FROM information_schema.referential_constraints rc
JOIN information_schema.key_column_usage kcu1
ON kcu1.constraint_name = rc.constraint_name
JOIN information_schema.key_column_usage kcu2
ON kcu2.constraint_name = rc.unique_constraint_name
WHERE kcu1.table_catalog = '' AND kcu1.table_schema = ''
AND kcu1.table_name = @tableName
ORDER BY rc.constraint_name, kcu1.ordinal_position`,
params: { tableName }
});
// Group foreign key columns by constraint name
const fkMap = new Map<string, { name: string; columns: string[]; referencedTable: string; referencedColumns: string[] }>();
for (const fk of foreignKeysResult) {
const constraintName = (fk as any).constraint_name as string;
const columnName = (fk as any).column_name as string;
const referencedTable = (fk as any).referenced_table as string;
const referencedColumn = (fk as any).referenced_column as string;
if (!fkMap.has(constraintName)) {
fkMap.set(constraintName, {
name: constraintName,
columns: [],
referencedTable,
referencedColumns: []
});
}
const entry = fkMap.get(constraintName);
if (entry) {
entry.columns.push(columnName);
entry.referencedColumns.push(referencedColumn);
}
}
foreignKeys = Array.from(fkMap.values());
} catch (error) {
console.error(`Error fetching foreign keys for table ${tableName}:`, error);
// Continue without foreign keys if the query fails
}
tables.push({
name: tableName,
columns,
indexes: indexes.length > 0 ? indexes : undefined,
foreignKeys: foreignKeys.length > 0 ? foreignKeys : undefined
});
}
return { tables };
}
/**
* Formats schema information as markdown
*
* @param schema The schema to format
* @returns A markdown string representation of the schema
*/
export function formatSchemaAsMarkdown(schema: SpannerSchema): string {
if (schema.tables.length === 0) {
return 'No tables found in the database.';
}
let markdown = '# Database Schema\n\n';
for (const table of schema.tables) {
markdown += `## Table: ${table.name}\n\n`;
// Columns
markdown += '### Columns\n\n';
markdown += '| Column | Type | Nullable |\n';
markdown += '|--------|------|----------|\n';
for (const column of table.columns) {
markdown += `| ${column.name} | ${column.type} | ${column.nullable ? 'YES' : 'NO'} |\n`;
}
markdown += '\n';
// Indexes
if (table.indexes && table.indexes.length > 0) {
markdown += '### Indexes\n\n';
markdown += '| Name | Columns | Unique |\n';
markdown += '|------|---------|--------|\n';
for (const index of table.indexes) {
markdown += `| ${index.name} | ${index.columns.join(', ')} | ${index.unique ? 'YES' : 'NO'} |\n`;
}
markdown += '\n';
}
// Foreign Keys
if (table.foreignKeys && table.foreignKeys.length > 0) {
markdown += '### Foreign Keys\n\n';
markdown += '| Name | Columns | Referenced Table | Referenced Columns |\n';
markdown += '|------|---------|-----------------|-------------------|\n';
for (const fk of table.foreignKeys) {
markdown += `| ${fk.name} | ${fk.columns.join(', ')} | ${fk.referencedTable} | ${fk.referencedColumns.join(', ')} |\n`;
}
markdown += '\n';
}
markdown += '---\n\n';
}
return markdown;
}