extract-sqlserver-db-tables-to-prisma-model.ts

const { PrismaClient } = require('@prisma/client');
const { writeFileSync } = require('fs');

async function generatePrismaModels() {
    // Connect to the database using the Prisma client
    const prisma = new PrismaClient({
        datasources: {
            db: {
                url: 'sqlserver://localhost:1433;database=mydb;user=sa;password=mypswd;trustServerCertificate=true',
            },
        },
    });

    // Get the list of tables in the database
    const tables = await prisma.$queryRaw`SELECT table_name FROM information_schema.tables`;

    console.log('Tables =>', tables);

    // Generate a Prisma model for each table
    for (const table of tables) {
        const tableName = table.table_name;

        // Get the list of columns in the table
        const columns = await prisma.$queryRaw`SELECT column_name, data_type, is_nullable, column_default FROM information_schema.columns`;

        console.log('columns', columns);

        // Generate the Prisma model schema
        const schema = `model ${tableName} {
  ${columns
                .map(
                    (column) =>
                        `${column.column_name} ${getPrismaType(column)}${column.is_nullable === 'YES' ? '?' : ''
                        }${getDefault(column)}`,
                )
                .join('\n  ')}
}`;
        // Write the schema to a file
        writeFileSync(`./prisma/gen/${tableName}.prisma`, schema);
    }

    // await prisma.disconnect();

    console.log('Prisma models generated successfully.');
}

function getPrismaType(column) {
    switch (column.data_type) {
        case 'int':
            return 'Int';
        case 'varchar':
        case 'nvarchar':
        case 'text':
            return 'String';
        case 'bigint':
            return 'BigInt';
        case 'boolean':
            return 'Boolean';
        case 'datetime2':
        case 'timestamp with time zone':
        case 'timestamp without time zone':
            return 'DateTime';
        case 'json':
        case 'jsonb':
            return 'Json';
        case 'numeric':
        case 'double precision':
            return 'Float';
        default:
            return column.data_type;
    }
}

function getDefault(column) {
    if (column.column_default) {
        const defaultValue = column.column_default.replace(/^'(.*)'$/, '$1');
        return ` @default(${defaultValue})`;
    } else {
        return '';
    }
}

generatePrismaModels().catch((error) => {
    console.error(error);
});