Skip to content

Database diagrams editor that allows you to visualize and design your DB with a single query.

22.7 KB
Raw Blame
import { describe, it, expect } from 'vitest';
import { exportBaseSQL } from '../export-sql-script';
import { exportPostgreSQL } from '../export-per-type/postgresql';
import { exportMySQL } from '../export-per-type/mysql';
import { exportMSSQL } from '../export-per-type/mssql';
import { exportSQLite } from '../export-per-type/sqlite';
import { DatabaseType } from '@/lib/domain/database-type';
import type { Diagram } from '@/lib/domain/diagram';
import type { DBTable } from '@/lib/domain/db-table';
import type { DBField } from '@/lib/domain/db-field';

describe('SQL Export Tests', () => {
    let idCounter = 0;
    const testId = () => `test-id-${++idCounter}`;
    const testTime = Date.now();

    const createField = (overrides: Partial<DBField>): DBField =>
        ({
            id: testId(),
            name: 'field',
            type: { id: 'text', name: 'text' },
            primaryKey: false,
            nullable: true,
            unique: false,
            createdAt: testTime,
            ...overrides,
        }) as DBField;

    const createTable = (overrides: Partial<DBTable>): DBTable =>
        ({
            id: testId(),
            name: 'table',
            fields: [],
            indexes: [],
            createdAt: testTime,
            x: 0,
            y: 0,
            width: 200,
            ...overrides,
        }) as DBTable;

    const createDiagram = (overrides: Partial<Diagram>): Diagram =>
        ({
            id: testId(),
            name: 'diagram',
            databaseType: DatabaseType.GENERIC,
            tables: [],
            relationships: [],
            createdAt: testTime,
            updatedAt: testTime,
            ...overrides,
        }) as Diagram;

    const createTestDiagramWithPKIndex = (
        databaseType: DatabaseType
    ): { diagram: Diagram; fieldId: string } => {
        const fieldId = testId();
        const diagram = createDiagram({
            id: testId(),
            name: 'PK Test',
            databaseType,
            tables: [
                createTable({
                    id: testId(),
                    name: 'table_1',
                    schema: 'public',
                    fields: [
                        createField({
                            id: fieldId,
                            name: 'id',
                            type: { id: 'bigint', name: 'bigint' },
                            primaryKey: true,
                            nullable: false,
                            unique: false,
                        }),
                    ],
                    indexes: [
                        {
                            id: testId(),
                            name: '', // Empty name indicates auto-generated PK index
                            unique: true,
                            fieldIds: [fieldId],
                            createdAt: testTime,
                            isPrimaryKey: true,
                        },
                    ],
                }),
            ],
            relationships: [],
        });
        return { diagram, fieldId };
    };

    describe('Primary Key Index Export', () => {
        describe('exportBaseSQL', () => {
            it('should export PRIMARY KEY without CONSTRAINT for PostgreSQL', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.POSTGRESQL
                );

                const sql = exportBaseSQL({
                    diagram,
                    targetDatabaseType: DatabaseType.POSTGRESQL,
                });

                expect(sql).toContain('PRIMARY KEY ("id")');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('should export PRIMARY KEY without CONSTRAINT for MySQL', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.MYSQL
                );

                const sql = exportBaseSQL({
                    diagram,
                    targetDatabaseType: DatabaseType.MYSQL,
                });

                expect(sql).toContain('PRIMARY KEY');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('should export PRIMARY KEY without CONSTRAINT for SQL Server', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.SQL_SERVER
                );

                const sql = exportBaseSQL({
                    diagram,
                    targetDatabaseType: DatabaseType.SQL_SERVER,
                });

                expect(sql).toContain('PRIMARY KEY');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('should export PRIMARY KEY without CONSTRAINT for SQLite', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.SQLITE
                );

                const sql = exportBaseSQL({
                    diagram,
                    targetDatabaseType: DatabaseType.SQLITE,
                });

                expect(sql).toContain('PRIMARY KEY');
                expect(sql).not.toContain('CONSTRAINT');
            });
        });

        describe('Database-specific exporters', () => {
            it('exportPostgreSQL: should export PRIMARY KEY without CONSTRAINT', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.POSTGRESQL
                );

                const sql = exportPostgreSQL({ diagram });

                expect(sql).toContain('PRIMARY KEY ("id")');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('exportMySQL: should export PRIMARY KEY without CONSTRAINT', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.MYSQL
                );

                const sql = exportMySQL({ diagram });

                expect(sql).toContain('PRIMARY KEY (`id`)');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('exportMSSQL: should export PRIMARY KEY without CONSTRAINT', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.SQL_SERVER
                );

                const sql = exportMSSQL({ diagram });

                expect(sql).toContain('PRIMARY KEY ([id])');
                expect(sql).not.toContain('CONSTRAINT');
            });

            it('exportSQLite: should export PRIMARY KEY without CONSTRAINT', () => {
                const { diagram } = createTestDiagramWithPKIndex(
                    DatabaseType.SQLITE
                );

                const sql = exportSQLite({ diagram });

                // SQLite uses inline PRIMARY KEY for single integer columns
                expect(sql).toContain('PRIMARY KEY');
                expect(sql).not.toContain('CONSTRAINT');
            });
        });
    });

    describe('Unique Constraint Index Export', () => {
        it('should not generate CREATE UNIQUE INDEX for single-column unique fields in PostgreSQL', () => {
            const fieldId = testId();
            const diagram = createDiagram({
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        name: 'orders_copy',
                        schema: 'public',
                        fields: [
                            createField({
                                id: fieldId,
                                name: 'id',
                                type: { id: 'bigserial', name: 'bigserial' },
                                primaryKey: false,
                                nullable: false,
                                unique: true,
                            }),
                            createField({
                                name: 'created_at',
                                type: {
                                    id: 'timestamptz',
                                    name: 'timestamptz',
                                },
                                nullable: false,
                            }),
                        ],
                        indexes: [
                            {
                                id: testId(),
                                name: 'orders_copy_id_key',
                                fieldIds: [fieldId],
                                unique: true,
                                isPrimaryKey: false,
                                createdAt: testTime,
                            },
                        ],
                    }),
                ],
            });

            const sql = exportPostgreSQL({ diagram });

            // Should have inline UNIQUE
            expect(sql).toContain('"id" bigserial NOT NULL UNIQUE');
            // Should NOT have separate CREATE UNIQUE INDEX for the unique field
            expect(sql).not.toContain('CREATE UNIQUE INDEX');
        });

        it('should still generate CREATE UNIQUE INDEX for multi-column unique indexes in PostgreSQL', () => {
            const fieldId1 = testId();
            const fieldId2 = testId();
            const diagram = createDiagram({
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        name: 'test_table',
                        schema: 'public',
                        fields: [
                            createField({
                                id: fieldId1,
                                name: 'col_a',
                                type: { id: 'text', name: 'text' },
                                nullable: false,
                                unique: false,
                            }),
                            createField({
                                id: fieldId2,
                                name: 'col_b',
                                type: { id: 'text', name: 'text' },
                                nullable: false,
                                unique: false,
                            }),
                        ],
                        indexes: [
                            {
                                id: testId(),
                                name: 'test_table_unique_idx',
                                fieldIds: [fieldId1, fieldId2],
                                unique: true,
                                isPrimaryKey: false,
                                createdAt: testTime,
                            },
                        ],
                    }),
                ],
            });

            const sql = exportPostgreSQL({ diagram });

            // Should have CREATE UNIQUE INDEX for multi-column unique constraint
            expect(sql).toContain('CREATE UNIQUE INDEX');
            expect(sql).toContain('"col_a", "col_b"');
        });

        it('should generate CREATE UNIQUE INDEX for single-column unique index when field is not marked unique', () => {
            const fieldId = testId();
            const diagram = createDiagram({
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        name: 'test_table',
                        schema: 'public',
                        fields: [
                            createField({
                                id: fieldId,
                                name: 'email',
                                type: { id: 'text', name: 'text' },
                                nullable: false,
                                unique: false, // Field not marked as unique
                            }),
                        ],
                        indexes: [
                            {
                                id: testId(),
                                name: 'test_table_email_key',
                                fieldIds: [fieldId],
                                unique: true,
                                isPrimaryKey: false,
                                createdAt: testTime,
                            },
                        ],
                    }),
                ],
            });

            const sql = exportPostgreSQL({ diagram });

            // Should NOT have inline UNIQUE (field.unique is false)
            expect(sql).not.toContain('UNIQUE,');
            expect(sql).not.toContain('NOT NULL UNIQUE');
            // Should have CREATE UNIQUE INDEX since the field doesn't have inline UNIQUE
            expect(sql).toContain('CREATE UNIQUE INDEX');
        });
    });

    describe('exportBaseSQL with foreign key relationships', () => {
        it('should export PostgreSQL diagram with two tables and a foreign key relationship', () => {
            const diagram = createDiagram({
                id: 'ee570f766a15',
                name: 'Diagram 1',
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        id: '10hmfnjq496fk2p0vgl5cooal',
                        name: 'user_profiles',
                        x: 1701,
                        y: -100,
                        schema: 'public',
                        color: '#8eb7ff',
                        isView: false,
                        order: 1,
                        fields: [
                            createField({
                                id: '0moy7ijg3k2azxhsbsjg0n94f',
                                name: 'id',
                                type: { id: 'bigint', name: 'bigint' },
                                unique: true,
                                nullable: false,
                                primaryKey: true,
                            }),
                            createField({
                                id: '3vo5j6c208kox4qt2i6lpx9x8',
                                name: 'user_id',
                                type: { id: 'bigint', name: 'bigint' },
                                unique: false,
                                nullable: true,
                                primaryKey: false,
                            }),
                        ],
                        indexes: [
                            {
                                id: '4sjv9srxk3ny3j4aptozbjzg2',
                                name: '',
                                fieldIds: ['0moy7ijg3k2azxhsbsjg0n94f'],
                                unique: true,
                                isPrimaryKey: true,
                                createdAt: testTime,
                            },
                        ],
                    }),
                    createTable({
                        id: 'tooohdxmn7kw9u1sv77o7x0pb',
                        name: 'users',
                        x: 1229,
                        y: -92,
                        schema: 'public',
                        color: '#8eb7ff',
                        isView: false,
                        order: 0,
                        fields: [
                            createField({
                                id: 'ahppb8odc54hqyenfslw37xv1',
                                name: 'id',
                                type: { id: 'bigint', name: 'bigint' },
                                unique: true,
                                nullable: false,
                                primaryKey: true,
                            }),
                        ],
                        indexes: [
                            {
                                id: 'y7rauhiqqwywx7zz1z59pig0r',
                                name: '',
                                fieldIds: ['ahppb8odc54hqyenfslw37xv1'],
                                unique: true,
                                isPrimaryKey: true,
                                createdAt: testTime,
                            },
                        ],
                    }),
                ],
                relationships: [
                    {
                        id: 's8leta6fjmm86fcwfpjngq65c',
                        name: 'users_id_fk',
                        sourceSchema: 'public',
                        sourceTableId: 'tooohdxmn7kw9u1sv77o7x0pb',
                        targetSchema: 'public',
                        targetTableId: '10hmfnjq496fk2p0vgl5cooal',
                        sourceFieldId: 'ahppb8odc54hqyenfslw37xv1',
                        targetFieldId: '3vo5j6c208kox4qt2i6lpx9x8',
                        sourceCardinality: 'one',
                        targetCardinality: 'one',
                        createdAt: testTime,
                    },
                ],
            });

            const expectedSql = `CREATE SCHEMA IF NOT EXISTS "public";

CREATE TABLE "public"."user_profiles" (
    "id" bigint NOT NULL,
    "user_id" bigint,
    PRIMARY KEY ("id")
);

CREATE TABLE "public"."users" (
    "id" bigint NOT NULL,
    PRIMARY KEY ("id")
);

-- Foreign key constraints
-- Schema: public
ALTER TABLE "public"."user_profiles" ADD CONSTRAINT "fk_user_profiles_user_id_users_id" FOREIGN KEY("user_id") REFERENCES "public"."users"("id");`;

            const sql = exportBaseSQL({
                diagram,
                targetDatabaseType: DatabaseType.POSTGRESQL,
            });

            expect(sql.trim()).toBe(expectedSql.trim());
        });

        it('should place FK on target table for 1:1 relationships in DBML flow', () => {
            // This tests the generic code path used by DBML export (isDBMLFlow: true)
            const usersTableId = 'users-table-id';
            const profilesTableId = 'profiles-table-id';
            const usersIdFieldId = 'users-id-field';
            const profilesUserIdFieldId = 'profiles-user-id-field';

            const diagram = createDiagram({
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        id: usersTableId,
                        name: 'users',
                        schema: 'public',
                        fields: [
                            createField({
                                id: usersIdFieldId,
                                name: 'id',
                                type: { id: 'bigint', name: 'bigint' },
                                primaryKey: true,
                                nullable: false,
                            }),
                        ],
                    }),
                    createTable({
                        id: profilesTableId,
                        name: 'profiles',
                        schema: 'public',
                        fields: [
                            createField({
                                id: profilesUserIdFieldId,
                                name: 'user_id',
                                type: { id: 'bigint', name: 'bigint' },
                                nullable: true,
                            }),
                        ],
                    }),
                ],
                relationships: [
                    {
                        id: 'rel-1',
                        name: 'profiles_user_fk',
                        sourceSchema: 'public',
                        sourceTableId: usersTableId, // users is source (parent)
                        targetSchema: 'public',
                        targetTableId: profilesTableId, // profiles is target (child with FK)
                        sourceFieldId: usersIdFieldId,
                        targetFieldId: profilesUserIdFieldId,
                        sourceCardinality: 'one',
                        targetCardinality: 'one',
                        createdAt: testTime,
                    },
                ],
            });

            const sql = exportBaseSQL({
                diagram,
                targetDatabaseType: DatabaseType.POSTGRESQL,
                isDBMLFlow: true, // Use the generic code path
            });

            // For 1:1 relationships, FK should be on target table (profiles)
            // The ALTER TABLE should be on profiles, referencing users
            expect(sql).toContain(
                'ALTER TABLE "public"."profiles" ADD CONSTRAINT profiles_user_fk FOREIGN KEY ("user_id") REFERENCES "public"."users" ("id")'
            );
        });

        it('should place FK on many side for one-to-many relationships', () => {
            const ordersTableId = 'orders-table-id';
            const customersTableId = 'customers-table-id';
            const ordersCustomerIdFieldId = 'orders-customer-id-field';
            const customersIdFieldId = 'customers-id-field';

            const diagram = createDiagram({
                databaseType: DatabaseType.POSTGRESQL,
                tables: [
                    createTable({
                        id: customersTableId,
                        name: 'customers',
                        schema: 'public',
                        fields: [
                            createField({
                                id: customersIdFieldId,
                                name: 'id',
                                type: { id: 'bigint', name: 'bigint' },
                                primaryKey: true,
                                nullable: false,
                            }),
                        ],
                    }),
                    createTable({
                        id: ordersTableId,
                        name: 'orders',
                        schema: 'public',
                        fields: [
                            createField({
                                id: ordersCustomerIdFieldId,
                                name: 'customer_id',
                                type: { id: 'bigint', name: 'bigint' },
                                nullable: true,
                            }),
                        ],
                    }),
                ],
                relationships: [
                    {
                        id: 'rel-2',
                        name: 'orders_customer_fk',
                        sourceSchema: 'public',
                        sourceTableId: customersTableId, // customers is one
                        targetSchema: 'public',
                        targetTableId: ordersTableId, // orders is many
                        sourceFieldId: customersIdFieldId,
                        targetFieldId: ordersCustomerIdFieldId,
                        sourceCardinality: 'one',
                        targetCardinality: 'many',
                        createdAt: testTime,
                    },
                ],
            });

            const sql = exportBaseSQL({
                diagram,
                targetDatabaseType: DatabaseType.POSTGRESQL,
                isDBMLFlow: true,
            });

            // For one:many, FK should be on the many side (orders)
            expect(sql).toContain(
                'ALTER TABLE "public"."orders" ADD CONSTRAINT orders_customer_fk FOREIGN KEY ("customer_id") REFERENCES "public"."customers" ("id")'
            );
        });
    });
});