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 SQL Server DDL.
* Converts PostgreSQL-specific types and features to SQL Server 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,
formatMSSQLTableComment,
isFunction,
isKeyword,
strHasQuotes,
} from '../common';
import {
postgresqlIndexTypeToSQLServer,
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 SQL Server equivalent
*/
function convertPostgresDefaultToMSSQL(field: DBField): string {
if (!field.default) {
return '';
}
const defaultValue = field.default.trim();
const defaultLower = defaultValue.toLowerCase();
// Handle sequences (nextval) - these become IDENTITY, no default needed
if (defaultLower.includes('nextval')) {
return '';
}
// Handle PostgreSQL now() -> SQL Server GETDATE()
if (defaultLower === 'now()' || defaultLower === 'current_timestamp') {
return 'GETDATE()';
}
// Handle UUID generation functions
if (
defaultLower.includes('gen_random_uuid') ||
defaultLower.includes('uuid_generate')
) {
return 'NEWID()';
}
// Handle JSONB/JSON functions
if (
defaultLower.includes('json_build_object') ||
defaultLower.includes('jsonb_build_object')
) {
return "N'{}'";
}
if (
defaultLower.includes('json_build_array') ||
defaultLower.includes('jsonb_build_array')
) {
return "N'[]'";
}
// Handle empty array defaults
if (
defaultLower === "'{}'::text[]" ||
defaultLower.match(/'\{\}'::.*\[\]/)
) {
return "N'[]'";
}
// Handle array literals
if (defaultLower.startsWith('array[')) {
const content = defaultValue.match(/ARRAY\[(.*?)\]/i)?.[1] || '';
return `N'[${content}]'`;
}
// Handle PostgreSQL true/false -> SQL Server 1/0
if (defaultLower === 'true') {
return '1';
}
if (defaultLower === 'false') {
return '0';
}
// Strip PostgreSQL type casts
const withoutCast = defaultValue.split('::')[0].trim();
// Handle SQL Server specific syntax for wrapped defaults
if (withoutCast.match(/^\(\(.*\)\)$/)) {
return withoutCast.replace(/^\(\(|\)\)$/g, '');
}
// If it's a function call, try to map to SQL Server
if (isFunction(withoutCast)) {
return withoutCast;
}
// If it's a keyword, keep it
if (isKeyword(withoutCast)) {
return withoutCast;
}
// If already quoted, convert to N'' style
if (strHasQuotes(withoutCast)) {
// Convert single quotes to N'' style
if (withoutCast.startsWith("'") && withoutCast.endsWith("'")) {
return `N${withoutCast}`;
}
return withoutCast;
}
// If it's a number, keep it
if (/^-?\d+(\.\d+)?$/.test(withoutCast)) {
return withoutCast;
}
// For other cases, wrap in N''
return `N'${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 SQL Server type with size/precision handling
* @param field - The field to map
* @param customTypes - Custom types defined in the schema
* @param isIndexed - Whether this field is used in an index (affects MAX type handling)
*/
function mapPostgresTypeToMSSQL(
field: DBField,
customTypes: DBCustomType[],
isIndexed: boolean = false
): {
typeName: string;
inlineComment: string | null;
} {
const originalType = field.type.name.toLowerCase();
let inlineComment: string | null = null;
// SQL Server has a 900-byte limit for index keys. NVARCHAR uses 2 bytes per char,
// so 450 chars is the max for indexed NVARCHAR columns.
const indexSafeNvarcharSize = 'NVARCHAR(450)';
// Handle array types
if (field.isArray || originalType.endsWith('[]')) {
// Arrays used in indexes need bounded size (though this is unusual)
const arrayType = isIndexed ? indexSafeNvarcharSize : 'NVARCHAR(MAX)';
return {
typeName: arrayType,
inlineComment: `Was: ${field.type.name} (PostgreSQL array${isIndexed ? ', size limited for index' : ', stored as JSON'})`,
};
}
// Check for custom types (ENUM or composite)
const customType = findCustomType(field.type.name, customTypes);
if (customType) {
if (customType.kind === 'enum') {
// ENUMs become NVARCHAR(255)
return {
typeName: 'NVARCHAR(255)',
inlineComment: null, // Inline comment handled separately via getEnumValuesComment
};
} else if (customType.kind === 'composite') {
// Composite types become NVARCHAR(MAX) as JSON (shouldn't be indexed normally)
const compositeType = isIndexed
? indexSafeNvarcharSize
: 'NVARCHAR(MAX)';
return {
typeName: compositeType,
inlineComment: `Was: ${field.type.name} (PostgreSQL composite type${isIndexed ? ', size limited for index' : ''})`,
};
}
}
// Look up mapping
const mapping = getTypeMapping(originalType, 'sqlserver');
const effectiveMapping = mapping || getFallbackTypeMapping('sqlserver');
let typeName = effectiveMapping.targetType;
// If indexed and type contains (MAX), replace with bounded size for index compatibility
// SQL Server cannot use MAX types as index keys
if (isIndexed && typeName.includes('(MAX)')) {
typeName = typeName.replace('(MAX)', '(450)');
inlineComment = `Was: ${field.type.name} (size limited for index)`;
}
// Handle size/precision
if (field.characterMaximumLength) {
if (
typeName === 'VARCHAR' ||
typeName === 'NVARCHAR' ||
typeName === 'CHAR' ||
typeName === 'NCHAR' ||
typeName === 'VARBINARY'
) {
typeName = `${typeName}(${field.characterMaximumLength})`;
}
} else if (effectiveMapping.defaultLength) {
if (
typeName === 'VARCHAR' ||
typeName === 'NVARCHAR' ||
typeName === 'CHAR' ||
typeName === 'NCHAR'
) {
typeName = `${typeName}(${effectiveMapping.defaultLength})`;
}
}
if (field.precision !== undefined && field.scale !== undefined) {
if (
typeName === 'DECIMAL' ||
typeName === 'NUMERIC' ||
typeName === 'DATETIME2' ||
typeName === 'DATETIMEOFFSET'
) {
if (typeName === 'DATETIME2' || typeName === 'DATETIMEOFFSET') {
// For datetime types, only precision applies (fractional seconds)
if (field.precision !== null && field.precision <= 7) {
typeName = `${typeName}(${field.precision})`;
}
} else {
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 (but don't override index-related comment)
if (effectiveMapping.includeInlineComment && !inlineComment) {
inlineComment = `Was: ${field.type.name}`;
}
return { typeName, inlineComment };
}
/**
* Check if a field should have IDENTITY
*/
function isIdentity(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 {
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 SQL Server DDL
*/
export function exportPostgreSQLToMSSQL({
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.SQL_SERVER
);
// Build output
let sqlScript = formatWarningsHeader(
unsupportedFeatures,
'PostgreSQL',
'SQL Server'
);
if (!onlyRelationships) {
// Create 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 += `IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = '${schema}')\nBEGIN\n EXEC('CREATE SCHEMA [${schema}]');\nEND;\nGO\n`;
});
if (schemas.size > 0) {
sqlScript += '\n';
}
// Generate table creation SQL
sqlScript += tables
.map((table: DBTable) => {
// Skip views
if (table.isView) {
return '';
}
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;
// Compute which fields are used in indexes (for type size limiting)
// SQL Server has a 900-byte limit for index keys
const indexedFieldIds = new Set<string>();
table.indexes.forEach((idx) => {
idx.fieldIds.forEach((fieldId) => {
indexedFieldIds.add(fieldId);
});
});
// Also add primary key fields as they are indexed
primaryKeyFields.forEach((f) => {
indexedFieldIds.add(f.id);
});
// Also add unique fields as they create implicit indexes
table.fields.forEach((f) => {
if (f.unique) {
indexedFieldIds.add(f.id);
}
});
const fieldDefinitions = table.fields.map(
(field: DBField, index: number, allFields: DBField[]) => {
const fieldName = `[${field.name}]`;
// Check if this field is used in an index
const isIndexed = indexedFieldIds.has(field.id);
// Map type to SQL Server
const { typeName, inlineComment } =
mapPostgresTypeToMSSQL(
field,
customTypes,
isIndexed
);
// 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 IDENTITY
const identity = isIdentity(field)
? ' IDENTITY(1,1)'
: '';
// Only add UNIQUE constraint if not primary key
const unique =
!field.primaryKey && field.unique ? ' UNIQUE' : '';
// Handle default value
const convertedDefault =
convertPostgresDefaultToMSSQL(field);
const defaultValue =
convertedDefault && !identity
? ` DEFAULT ${convertedDefault}`
: '';
// 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}${identity}${unique}${defaultValue}${needsComma ? ',' : ''}${sqlInlineComment}`;
}
);
return `${
table.comments
? formatMSSQLTableComment(table.comments)
: ''
}CREATE TABLE ${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);\nGO${
// 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 matches primary key
if (
primaryKeyFields.length ===
indexFields.length &&
primaryKeyFields.every((pk) =>
indexFields.some(
(field) =>
field && field.id === pk.id
)
)
) {
return '';
}
// Get index type conversion
const indexType = (
index.type || 'btree'
).toLowerCase();
const indexTypeMapping =
postgresqlIndexTypeToSQLServer[indexType];
const indexInlineComment =
getIndexInlineComment(index, 'sqlserver');
const indexName = table.schema
? `[${table.schema}_${index.name}]`
: `[${index.name}]`;
const indexFieldNames = indexFields
.map((field) =>
field ? `[${field.name}]` : ''
)
.filter(Boolean);
// SQL Server has 32 column limit
if (indexFieldNames.length > 32) {
console.warn(
`Warning: Index ${indexName} has ${indexFieldNames.length} columns. Truncating to 32.`
);
indexFieldNames.length = 32;
}
const commentStr = indexInlineComment
? ` -- ${indexInlineComment}`
: '';
return indexFieldNames.length > 0
? `CREATE ${index.unique ? 'UNIQUE ' : ''}${indexTypeMapping?.targetType === 'CLUSTERED' ? 'CLUSTERED ' : 'NONCLUSTERED '}INDEX ${indexName} ON ${tableName} (${indexFieldNames.join(', ')});${commentStr}`
: '';
})
.filter(Boolean)
.sort((a, b) => a.localeCompare(b));
return validIndexes.length > 0
? `\n-- Indexes\n${validIndexes.join('\nGO\n')}\nGO`
: '';
})()
}`;
})
.filter(Boolean)
.join('\n');
// Add extended properties for table/column comments
const commentStatements: string[] = [];
for (const table of tables) {
if (table.isView) continue;
const schemaName = table.schema || 'dbo';
if (table.comments) {
commentStatements.push(
`EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'${table.comments.replace(/'/g, "''")}', @level0type=N'SCHEMA', @level0name=N'${schemaName}', @level1type=N'TABLE', @level1name=N'${table.name}';`
);
}
for (const field of table.fields) {
if (field.comments) {
commentStatements.push(
`EXEC sp_addextendedproperty @name=N'MS_Description', @value=N'${field.comments.replace(/'/g, "''")}', @level0type=N'SCHEMA', @level0name=N'${schemaName}', @level1type=N'TABLE', @level1name=N'${table.name}', @level2type=N'COLUMN', @level2name=N'${field.name}';`
);
}
}
}
if (commentStatements.length > 0) {
sqlScript += '\n-- Table and column descriptions\n';
sqlScript += commentStatements.join('\nGO\n');
sqlScript += '\nGO\n';
}
}
// Generate foreign keys
if (relationships.length > 0) {
sqlScript += '\n-- Foreign key constraints\n';
// Process relationships and group by schema
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 null;
}
const sourceField = sourceTable.fields.find(
(f) => f.id === r.sourceFieldId
);
const targetField = targetTable.fields.find(
(f) => f.id === r.targetFieldId
);
if (!sourceField || !targetField) {
return null;
}
// Determine FK placement 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 null;
} 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}]`;
return {
schema: fkTable.schema || 'dbo',
sql: `ALTER TABLE ${fkTableName} ADD CONSTRAINT [${r.name || `fk_${fkTable.name}_${fkField.name}`}] FOREIGN KEY([${fkField.name}]) REFERENCES ${refTableName}([${refField.name}]);`,
};
})
.filter(Boolean) as { schema: string; sql: string }[];
// Group by schema
const fksBySchema = foreignKeys.reduce(
(acc, fk) => {
if (!acc[fk.schema]) {
acc[fk.schema] = [];
}
acc[fk.schema].push(fk.sql);
return acc;
},
{} as Record<string, string[]>
);
// Sort schemas and output
const sortedSchemas = Object.keys(fksBySchema).sort();
const fkSql = sortedSchemas
.map((schema, index) => {
const schemaFks = fksBySchema[schema].join('\nGO\n');
if (index === 0) {
return `-- Schema: ${schema}\n${schemaFks}`;
} else {
return `\n-- Schema: ${schema}\n${schemaFks}`;
}
})
.join('\n');
sqlScript += fkSql;
sqlScript += '\nGO\n';
}
return sqlScript;
}