Database diagrams editor that allows you to visualize and design your DB with a single query.
TypeScript databasedbdb-migrationeditorerdmariadbmssqlmysqlpostgresqlreactreact-flowschema-migrationssqlsqlitetypescriptvisualizationxyflow
import type { DBCheckConstraint, DBIndex, DBTable } from '@/lib/domain';
import {
DatabaseType,
generateTableKey,
schemaNameToDomainSchemaName,
} from '@/lib/domain';
import type { DatabaseMetadata } from '../metadata-types/database-metadata';
import type { TableInfo } from '../metadata-types/table-info';
import type { CheckConstraintInfo } from '../metadata-types/check-constraint-info';
import { createAggregatedIndexes } from '../metadata-types/index-info';
import {
decodeBase64ToUtf16LE,
decodeBase64ToUtf8,
generateId,
} from '@/lib/utils';
import {
defaultTableColor,
materializedViewColor,
viewColor,
} from '@/lib/colors';
import { createFieldsFromMetadata } from './fields';
import { createIndexesFromMetadata } from './indexes';
export const decodeViewDefinition = (
databaseType: DatabaseType,
viewDefinition?: string
): string => {
if (!viewDefinition) {
return '';
}
let decodedViewDefinition: string;
if (databaseType === DatabaseType.SQL_SERVER) {
decodedViewDefinition = decodeBase64ToUtf16LE(viewDefinition);
} else {
decodedViewDefinition = decodeBase64ToUtf8(viewDefinition);
}
return decodedViewDefinition;
};
export const createTablesFromMetadata = ({
databaseMetadata,
databaseType,
}: {
databaseMetadata: DatabaseMetadata;
databaseType: DatabaseType;
}): DBTable[] => {
const {
tables: tableInfos,
pk_info: primaryKeys,
columns,
indexes,
views: views,
check_constraints: checkConstraints,
} = databaseMetadata;
// Pre-compute view names for faster lookup if there are views
const viewNamesSet = new Set<string>();
const materializedViewNamesSet = new Set<string>();
if (views && views.length > 0) {
views.forEach((view) => {
const key = generateTableKey({
schemaName: view.schema,
tableName: view.view_name,
});
viewNamesSet.add(key);
if (
view.view_definition &&
decodeViewDefinition(databaseType, view.view_definition)
.toLowerCase()
.includes('materialized')
) {
materializedViewNamesSet.add(key);
}
});
}
// Pre-compute lookup maps for better performance
const columnsByTable = new Map<string, (typeof columns)[0][]>();
const indexesByTable = new Map<string, (typeof indexes)[0][]>();
const primaryKeysByTable = new Map<string, (typeof primaryKeys)[0][]>();
// Group columns by table
columns.forEach((col) => {
const key = generateTableKey({
schemaName: col.schema,
tableName: col.table,
});
if (!columnsByTable.has(key)) {
columnsByTable.set(key, []);
}
columnsByTable.get(key)!.push(col);
});
// Group indexes by table
indexes.forEach((idx) => {
const key = generateTableKey({
schemaName: idx.schema,
tableName: idx.table,
});
if (!indexesByTable.has(key)) {
indexesByTable.set(key, []);
}
indexesByTable.get(key)!.push(idx);
});
// Group primary keys by table
primaryKeys.forEach((pk) => {
const key = generateTableKey({
schemaName: pk.schema,
tableName: pk.table,
});
if (!primaryKeysByTable.has(key)) {
primaryKeysByTable.set(key, []);
}
primaryKeysByTable.get(key)!.push(pk);
});
// Group check constraints by table
const checkConstraintsByTable = new Map<string, CheckConstraintInfo[]>();
if (checkConstraints) {
checkConstraints.forEach((cc) => {
const key = generateTableKey({
schemaName: cc.schema,
tableName: cc.table,
});
if (!checkConstraintsByTable.has(key)) {
checkConstraintsByTable.set(key, []);
}
checkConstraintsByTable.get(key)!.push(cc);
});
}
const result = tableInfos.map((tableInfo: TableInfo) => {
const tableSchema = schemaNameToDomainSchemaName(tableInfo.schema);
const tableKey = generateTableKey({
schemaName: tableInfo.schema,
tableName: tableInfo.table,
});
// Use pre-computed lookups instead of filtering entire arrays
const tableIndexes = indexesByTable.get(tableKey) || [];
const tablePrimaryKeys = primaryKeysByTable.get(tableKey) || [];
const tableColumns = columnsByTable.get(tableKey) || [];
// Aggregate indexes with multiple columns
const aggregatedIndexes = createAggregatedIndexes({
tableInfo,
tableSchema,
tableIndexes,
});
const fields = createFieldsFromMetadata({
aggregatedIndexes,
tableColumns,
tablePrimaryKeys,
tableInfo,
tableSchema,
databaseType,
});
// Check for composite primary key and find matching index name
const primaryKeyFields = fields.filter((f) => f.primaryKey);
let pkMatchingIndexName: string | undefined;
let pkIndex: DBIndex | undefined;
if (primaryKeyFields.length >= 1) {
// We have a composite primary key, look for an index that matches all PK columns
const pkFieldNames = primaryKeyFields.map((f) => f.name).sort();
// Find an index that matches the primary key columns exactly
const matchingIndex = aggregatedIndexes.find((index) => {
const indexColumnNames = index.columns
.map((c) => c.name)
.sort();
return (
indexColumnNames.length === pkFieldNames.length &&
indexColumnNames.every((col, i) => col === pkFieldNames[i])
);
});
if (matchingIndex) {
pkMatchingIndexName = matchingIndex.name;
// Create a special PK index
pkIndex = {
id: generateId(),
name: matchingIndex.name,
unique: true,
fieldIds: primaryKeyFields.map((f) => f.id),
createdAt: Date.now(),
isPrimaryKey: true,
};
}
}
// Filter out the index that matches the composite PK (to avoid duplication)
const filteredAggregatedIndexes = pkMatchingIndexName
? aggregatedIndexes.filter(
(idx) => idx.name !== pkMatchingIndexName
)
: aggregatedIndexes;
const dbIndexes = createIndexesFromMetadata({
aggregatedIndexes: filteredAggregatedIndexes,
fields,
});
// Add the PK index if it exists
if (pkIndex) {
dbIndexes.push(pkIndex);
}
// Determine if the current table is a view by checking against pre-computed sets
const viewKey = generateTableKey({
schemaName: tableSchema,
tableName: tableInfo.table,
});
const isView = viewNamesSet.has(viewKey);
const isMaterializedView = materializedViewNamesSet.has(viewKey);
// Convert check constraints for this table
const tableCheckConstraints = checkConstraintsByTable.get(tableKey);
const dbCheckConstraints: DBCheckConstraint[] | undefined =
tableCheckConstraints && tableCheckConstraints.length > 0
? tableCheckConstraints.map((cc) => ({
id: generateId(),
expression: cc.expression,
createdAt: Date.now(),
}))
: undefined;
// Initial random positions; these will be adjusted later
return {
id: generateId(),
name: tableInfo.table,
schema: tableSchema,
x: Math.random() * 1000, // Placeholder X
y: Math.random() * 800, // Placeholder Y
fields,
indexes: dbIndexes,
checkConstraints: dbCheckConstraints,
color: isMaterializedView
? materializedViewColor
: isView
? viewColor
: defaultTableColor,
isView: isView,
isMaterializedView: isMaterializedView,
createdAt: Date.now(),
comments: tableInfo.comment ? tableInfo.comment : undefined,
};
});
return result;
};