Database diagrams editor that allows you to visualize and design your DB with a single query.
TypeScript databasedbdb-migrationeditorerdmariadbmssqlmysqlpostgresqlreactreact-flowschema-migrationssqlsqlitetypescriptvisualizationxyflow
/**
* Deterministic exporter for PostgreSQL diagrams to MySQL DDL.
* Converts PostgreSQL-specific types and features to MySQL equivalents,
* with comments for features that cannot be fully converted.
*/
import type { Diagram } from '@/lib/domain/diagram';
import type { DBTable } from '@/lib/domain/db-table';
import type { DBField } from '@/lib/domain/db-field';
import type { DBRelationship } from '@/lib/domain/db-relationship';
import type { DBCustomType } from '@/lib/domain/db-custom-type';
import {
exportFieldComment,
escapeSQLComment,
formatTableComment,
isFunction,
isKeyword,
strHasQuotes,
} from '../common';
import {
postgresqlIndexTypeToMySQL,
getTypeMapping,
getFallbackTypeMapping,
} from './type-mappings';
import {
detectUnsupportedFeatures,
formatWarningsHeader,
getIndexInlineComment,
} from '../unsupported-features';
import { DatabaseType } from '@/lib/domain/database-type';
/**
* Convert a PostgreSQL default value to MySQL equivalent
*/
function convertPostgresDefaultToMySQL(field: DBField): string {
if (!field.default) {
return '';
}
const defaultValue = field.default.trim();
const defaultLower = defaultValue.toLowerCase();
// Handle sequences (nextval) - these become AUTO_INCREMENT, no default needed
if (defaultLower.includes('nextval')) {
return '';
}
// Handle PostgreSQL now() -> MySQL CURRENT_TIMESTAMP
if (defaultLower === 'now()' || defaultLower === 'current_timestamp') {
return 'CURRENT_TIMESTAMP';
}
// Handle UUID generation functions
if (
defaultLower.includes('gen_random_uuid') ||
defaultLower.includes('uuid_generate')
) {
return '(UUID())';
}
// Handle JSONB/JSON functions
if (
defaultLower.includes('json_build_object') ||
defaultLower.includes('jsonb_build_object')
) {
return "'{}'";
}
if (
defaultLower.includes('json_build_array') ||
defaultLower.includes('jsonb_build_array')
) {
return "'[]'";
}
// Handle empty array defaults
if (
defaultLower === "'{}'::text[]" ||
defaultLower.match(/'\{\}'::.*\[\]/)
) {
return "'[]'";
}
// Handle array literals like ARRAY[1,2,3]
if (defaultLower.startsWith('array[')) {
const content = defaultValue.match(/ARRAY\[(.*?)\]/i)?.[1] || '';
return `'[${content}]'`;
}
// Strip PostgreSQL type casts
const withoutCast = defaultValue.split('::')[0].trim();
// If it's a function call, keep it (MySQL might support it)
if (isFunction(withoutCast)) {
return withoutCast;
}
// If it's a keyword, keep it
if (isKeyword(withoutCast)) {
return withoutCast;
}
// If already quoted, keep it
if (strHasQuotes(withoutCast)) {
return withoutCast;
}
// If it's a number, keep it
if (/^-?\d+(\.\d+)?$/.test(withoutCast)) {
return withoutCast;
}
// For other cases, add quotes
return `'${withoutCast.replace(/'/g, "''")}'`;
}
/**
* Check if a field type matches a custom enum or composite type
*/
function findCustomType(
fieldTypeName: string,
customTypes: DBCustomType[]
): DBCustomType | undefined {
const normalizedName = fieldTypeName.toLowerCase();
return customTypes.find((ct) => {
const ctName = ct.schema ? `${ct.schema}.${ct.name}` : ct.name;
return (
ctName.toLowerCase() === normalizedName ||
ct.name.toLowerCase() === normalizedName
);
});
}
/**
* Map a PostgreSQL type to MySQL type with size/precision handling
*/
function mapPostgresTypeToMySQL(
field: DBField,
customTypes: DBCustomType[]
): {
typeName: string;
inlineComment: string | null;
} {
const originalType = field.type.name.toLowerCase();
let inlineComment: string | null = null;
// Handle array types
if (field.isArray || originalType.endsWith('[]')) {
return {
typeName: 'JSON',
inlineComment: `Was: ${field.type.name} (PostgreSQL array)`,
};
}
// Check for custom types (ENUM or composite)
const customType = findCustomType(field.type.name, customTypes);
if (customType) {
if (customType.kind === 'enum') {
// ENUMs become VARCHAR(255)
return {
typeName: 'VARCHAR(255)',
inlineComment: null, // Inline comment handled separately via getEnumValuesComment
};
} else if (customType.kind === 'composite') {
// Composite types become JSON
return {
typeName: 'JSON',
inlineComment: `Was: ${field.type.name} (PostgreSQL composite type)`,
};
}
}
// Look up mapping
const mapping = getTypeMapping(originalType, 'mysql');
const effectiveMapping = mapping || getFallbackTypeMapping('mysql');
let typeName = effectiveMapping.targetType;
// Handle size/precision
if (field.characterMaximumLength) {
if (
typeName === 'VARCHAR' ||
typeName === 'CHAR' ||
typeName === 'VARBINARY'
) {
typeName = `${typeName}(${field.characterMaximumLength})`;
}
} else if (effectiveMapping.defaultLength) {
if (typeName === 'VARCHAR' || typeName === 'CHAR') {
typeName = `${typeName}(${effectiveMapping.defaultLength})`;
}
}
if (field.precision !== undefined && field.scale !== undefined) {
if (typeName === 'DECIMAL' || typeName === 'NUMERIC') {
typeName = `${typeName}(${field.precision}, ${field.scale})`;
}
} else if (field.precision !== undefined) {
if (typeName === 'DECIMAL' || typeName === 'NUMERIC') {
typeName = `${typeName}(${field.precision})`;
}
} else if (
effectiveMapping.defaultPrecision &&
(typeName === 'DECIMAL' || typeName === 'NUMERIC')
) {
typeName = `${typeName}(${effectiveMapping.defaultPrecision}, ${effectiveMapping.defaultScale || 0})`;
}
// Set inline comment if conversion note exists
if (effectiveMapping.includeInlineComment) {
inlineComment = `Was: ${field.type.name}`;
}
return { typeName, inlineComment };
}
/**
* Check if a field should have AUTO_INCREMENT
*/
function isAutoIncrement(field: DBField): boolean {
// Check increment flag
if (field.increment) {
return true;
}
// Check for serial types
const typeLower = field.type.name.toLowerCase();
if (
typeLower === 'serial' ||
typeLower === 'smallserial' ||
typeLower === 'bigserial'
) {
return true;
}
// Check for nextval in default
if (field.default?.toLowerCase().includes('nextval')) {
return true;
}
return false;
}
/**
* Build enum value comment for custom enum types
*/
function getEnumValuesComment(
fieldTypeName: string,
customTypes: DBCustomType[]
): string | null {
// Find matching enum type
const enumType = customTypes.find((ct) => {
const ctName = ct.schema ? `${ct.schema}.${ct.name}` : ct.name;
return (
ctName.toLowerCase() === fieldTypeName.toLowerCase() ||
ct.name.toLowerCase() === fieldTypeName.toLowerCase()
);
});
if (enumType?.kind === 'enum' && enumType.values?.length) {
return `PostgreSQL ENUM: '${enumType.values.join("', '")}'`;
}
return null;
}
/**
* Main export function: PostgreSQL diagram to MySQL DDL
*/
export function exportPostgreSQLToMySQL({
diagram,
onlyRelationships = false,
}: {
diagram: Diagram;
onlyRelationships?: boolean;
}): string {
if (!diagram.tables || !diagram.relationships) {
return '';
}
const tables = diagram.tables;
const relationships = diagram.relationships;
const customTypes = diagram.customTypes || [];
// Detect unsupported features for warnings header
const unsupportedFeatures = detectUnsupportedFeatures(
diagram,
DatabaseType.MYSQL
);
// Build output
let sqlScript = formatWarningsHeader(
unsupportedFeatures,
'PostgreSQL',
'MySQL'
);
if (!onlyRelationships) {
// Create databases (schemas) if they don't exist
const schemas = new Set<string>();
tables.forEach((table) => {
if (table.schema) {
schemas.add(table.schema);
}
});
schemas.forEach((schema) => {
sqlScript += `CREATE DATABASE IF NOT EXISTS \`${schema}\`;\n`;
});
if (schemas.size > 0) {
sqlScript += '\n';
}
// Generate table creation SQL
sqlScript += tables
.map((table: DBTable) => {
// Skip views
if (table.isView) {
return '';
}
// Use schema prefix if available
const tableName = table.schema
? `\`${table.schema}\`.\`${table.name}\``
: `\`${table.name}\``;
// Get primary key fields
const primaryKeyFields = table.fields.filter(
(f) => f.primaryKey
);
// Check if we have following constraints (for comma placement)
const validCheckConstraints = (
table.checkConstraints ?? []
).filter((c) => c.expression && c.expression.trim());
const hasFollowingConstraints =
primaryKeyFields.length > 0 ||
validCheckConstraints.length > 0;
const fieldDefinitions = table.fields.map(
(field: DBField, index: number, allFields: DBField[]) => {
const fieldName = `\`${field.name}\``;
// Map type to MySQL
const { typeName, inlineComment } =
mapPostgresTypeToMySQL(field, customTypes);
// Check for enum type and get values
const enumComment = getEnumValuesComment(
field.type.name,
customTypes
);
// Combine inline comments
const fullInlineComment = enumComment || inlineComment;
const notNull = field.nullable ? '' : ' NOT NULL';
// Handle auto_increment
const autoIncrement = isAutoIncrement(field)
? ' AUTO_INCREMENT'
: '';
// Only add UNIQUE constraint if the field is not part of the primary key
const unique =
!field.primaryKey && field.unique ? ' UNIQUE' : '';
// Handle default value
const convertedDefault =
convertPostgresDefaultToMySQL(field);
const defaultValue =
convertedDefault && !autoIncrement
? ` DEFAULT ${convertedDefault}`
: '';
// MySQL supports inline column comments
const comment = field.comments
? ` COMMENT '${escapeSQLComment(field.comments)}'`
: '';
// Build inline SQL comment for conversion notes
const sqlInlineComment = fullInlineComment
? ` -- ${fullInlineComment}`
: '';
// Determine if this field needs a trailing comma
const isLastField = index === allFields.length - 1;
const needsComma =
!isLastField || hasFollowingConstraints;
return `${exportFieldComment(field.comments ?? '')} ${fieldName} ${typeName}${notNull}${autoIncrement}${unique}${defaultValue}${comment}${needsComma ? ',' : ''}${sqlInlineComment}`;
}
);
return `${
table.comments ? formatTableComment(table.comments) : ''
}\nCREATE TABLE IF NOT EXISTS ${tableName} (\n${fieldDefinitions.join('\n')}${
// Add PRIMARY KEY as table constraint
primaryKeyFields.length > 0
? `\n PRIMARY KEY (${primaryKeyFields
.map((f) => `\`${f.name}\``)
.join(
', '
)})${validCheckConstraints.length > 0 ? ',' : ''}`
: ''
}${
// Add CHECK constraints (already computed above as validCheckConstraints)
validCheckConstraints.length > 0
? validCheckConstraints
.map(
(constraint, index) =>
`${index > 0 ? ',' : ''}\n CHECK (${constraint.expression})`
)
.join('')
: ''
}\n)${
// MySQL supports table comments
table.comments
? ` COMMENT='${escapeSQLComment(table.comments)}'`
: ''
};${
// Add indexes
(() => {
const validIndexes = table.indexes
.map((index) => {
// Skip primary key indexes
if (index.isPrimaryKey) {
return '';
}
// Get the list of fields for this index
const indexFields = index.fieldIds
.map((fieldId) => {
const field = table.fields.find(
(f) => f.id === fieldId
);
return field ? field : null;
})
.filter(Boolean);
// Skip if this index exactly matches the primary key fields
if (
primaryKeyFields.length ===
indexFields.length &&
primaryKeyFields.every((pk) =>
indexFields.some(
(field) =>
field && field.id === pk.id
)
)
) {
return '';
}
// Get index type conversion info
const indexType = (
index.type || 'btree'
).toLowerCase();
const indexTypeMapping =
postgresqlIndexTypeToMySQL[indexType];
const indexInlineComment =
getIndexInlineComment(index, 'mysql');
// Create index name
const fieldNamesForIndex = indexFields
.map((field) => field?.name || '')
.join('_');
const uniqueIndicator = index.unique
? '_unique'
: '';
const indexName = `\`idx_${table.name}_${fieldNamesForIndex}${uniqueIndicator}\``;
// Get the properly quoted field names
const indexFieldNames = indexFields
.map((field) =>
field ? `\`${field.name}\`` : ''
)
.filter(Boolean);
// Check for text/blob fields that need prefix length
const indexFieldsWithPrefix =
indexFieldNames.map((name) => {
const field = indexFields.find(
(f) => `\`${f?.name}\`` === name
);
if (!field) return name;
const typeName =
field.type.name.toLowerCase();
// Check if it maps to TEXT, JSON, or BLOB in MySQL
const mapping = getTypeMapping(
typeName,
'mysql'
);
const targetType = (
mapping?.targetType || ''
).toUpperCase();
if (
targetType === 'TEXT' ||
targetType === 'LONGTEXT' ||
targetType === 'MEDIUMTEXT' ||
targetType === 'JSON' ||
targetType === 'BLOB' ||
targetType === 'LONGBLOB'
) {
return `${name}(255)`;
}
return name;
});
const indexTypeStr =
indexTypeMapping?.targetType &&
indexTypeMapping.targetType !== 'BTREE'
? ` USING ${indexTypeMapping.targetType}`
: '';
const commentStr = indexInlineComment
? ` -- ${indexInlineComment}`
: '';
return indexFieldNames.length > 0
? `CREATE ${index.unique ? 'UNIQUE ' : ''}INDEX ${indexName} ON ${tableName}${indexTypeStr} (${indexFieldsWithPrefix.join(', ')});${commentStr}`
: '';
})
.filter(Boolean)
.sort((a, b) => a.localeCompare(b));
return validIndexes.length > 0
? `\n-- Indexes\n${validIndexes.join('\n')}`
: '';
})()
}`;
})
.filter(Boolean)
.join('\n');
}
// Generate foreign keys
if (relationships.length > 0) {
sqlScript += '\n-- Foreign key constraints\n';
const foreignKeys = relationships
.map((r: DBRelationship) => {
const sourceTable = tables.find(
(t) => t.id === r.sourceTableId
);
const targetTable = tables.find(
(t) => t.id === r.targetTableId
);
if (
!sourceTable ||
!targetTable ||
sourceTable.isView ||
targetTable.isView
) {
return '';
}
const sourceField = sourceTable.fields.find(
(f) => f.id === r.sourceFieldId
);
const targetField = targetTable.fields.find(
(f) => f.id === r.targetFieldId
);
if (!sourceField || !targetField) {
return '';
}
// Determine which table should have the foreign key based on cardinality
// - FK goes on the "many" side when cardinalities differ
// - FK goes on target when cardinalities are the same (one:one, many:many)
let fkTable, fkField, refTable, refField;
if (
r.sourceCardinality === 'many' &&
r.targetCardinality === 'many'
) {
// Many-to-many relationships need a junction table, skip
return '';
} else if (
r.sourceCardinality === 'many' &&
r.targetCardinality === 'one'
) {
// FK goes on source table (the many side)
fkTable = sourceTable;
fkField = sourceField;
refTable = targetTable;
refField = targetField;
} else {
// All other cases: FK goes on target table
fkTable = targetTable;
fkField = targetField;
refTable = sourceTable;
refField = sourceField;
}
const fkTableName = fkTable.schema
? `\`${fkTable.schema}\`.\`${fkTable.name}\``
: `\`${fkTable.name}\``;
const refTableName = refTable.schema
? `\`${refTable.schema}\`.\`${refTable.name}\``
: `\`${refTable.name}\``;
const constraintName = `\`fk_${fkTable.name}_${fkField.name}\``;
return `ALTER TABLE ${fkTableName} ADD CONSTRAINT ${constraintName} FOREIGN KEY(\`${fkField.name}\`) REFERENCES ${refTableName}(\`${refField.name}\`);`;
})
.filter(Boolean);
sqlScript += foreignKeys.join('\n');
}
return sqlScript;
}